-- The internal representation of a datetime is two 4-byte values
SELECT CURRENT_TIMESTAMP AS 'Today',
CAST(CURRENT_TIMESTAMP AS VARBINARY(8)) AS 'Two 4-byte Internal',
SUBSTRING(CAST(CURRENT_TIMESTAMP AS VARBINARY(8)), 1 , 4)
AS 'Days Since 1900-01-01',
SUBSTRING(CAST(CURRENT_TIMESTAMP AS VARBINARY(8)), 5 , 4)
AS 'Milliseconds Since Midnight',
CAST(SUBSTRING(CAST(CURRENT_TIMESTAMP AS VARBINARY(8)), 1 , 4) AS INT)
AS 'Days Represented as INT',
CAST(SUBSTRING(CAST(CURRENT_TIMESTAMP AS VARBINARY(8)), 5 , 4) AS INT)
AS 'Milliseconds Represented as INT';
-- Basic dates and date calculations
SELECT CAST('19000101 00:00' AS SMALLDATETIME) AS 'Min SMALLDATETIME',
CAST('20790606 23:59' AS SMALLDATETIME) AS 'Max SMALLDATETIME',
CAST('1753-01-01T00:00:00.000' AS DATETIME) AS 'Min DATETIME',
CAST('9999-12-31T23:59:59.997' AS DATETIME) AS 'Max DATETIME',
CAST(0 AS DATETIME) AS 'Base SQL Server DATETIME',
CURRENT_TIMESTAMP AS 'Current Date/Time',
DATEDIFF(DAY, 0, CURRENT_TIMESTAMP)
AS 'Current Date/Time as Number (days since 1900-01-01)',
CAST(DATEDIFF(DAY, 0, CURRENT_TIMESTAMP) AS DATETIME)
AS 'Today as Date',
CAST(DATEDIFF(DAY, -1, CURRENT_TIMESTAMP) AS DATETIME)
AS 'Tomorrow as Date',
CAST(DATEDIFF(DAY, 1, CURRENT_TIMESTAMP) AS DATETIME)
AS 'Yesterday as Date',
DATEADD(YEAR, DATEDIFF(YEAR, 0, CURRENT_TIMESTAMP)-1, 0)
AS 'First Day of Last Year',
DATEADD(YEAR, DATEDIFF(YEAR, 0, CURRENT_TIMESTAMP), 0)
AS 'First Day of This Year',
DATEDIFF(DAY, 0, DATEADD(YEAR, -1, CURRENT_TIMESTAMP))
AS 'Today One Year Ago as Number',
CAST(DATEDIFF(DAY, 0, DATEADD(YEAR, -1, CURRENT_TIMESTAMP)) AS DATETIME)
AS 'Today One Year Ago as Date',
DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0)
AS 'First Day of Current Month',
DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP) + 1, 0)
AS 'First Day of Next Month',
DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP) - 1, 0)
AS 'First Day of Prior Month',
DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP) + 1, 0) - 1
AS 'Last Day of Current Month',
DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), '01:30:00')
AS '1:30 am today',
CONVERT(VARCHAR(10), CURRENT_TIMESTAMP, 101) + ' 11:30:00.000'
AS '11:00 am today',
CONVERT(VARCHAR(10), CURRENT_TIMESTAMP, 101) + ' 17:00:00.000'
AS '5:00 pm today',
DATEADD(WEEK, DATEDIFF(WEEK, 0, CURRENT_TIMESTAMP), 0)
AS 'First Day of This Week',
DATEADD(WEEK, DATEDIFF(WEEK, 0, CURRENT_TIMESTAMP) - 1, 0)
AS 'First Day of Last Week',
DATEADD(WEEK, DATEDIFF(WEEK, 0, CURRENT_TIMESTAMP) + 1, 0)
AS 'First Day of Next Week';
-- Get Orders for Last Month
SELECT order_date
FROM Orders
WHERE order_date >= DATEADD(MONTH,
DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP) - 1, 0)
AND order_date < DATEADD(MONTH,
DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0);
-- Get Orders for Current Month
SELECT order_date
FROM Orders
WHERE order_date >= DATEADD(MONTH,
DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0)
AND order_date < DATEADD(MONTH,
DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP) + 1, 0);
-- Get Year to Date Orders
SELECT order_date
FROM Orders
WHERE order_date >= DATEADD(YEAR,
DATEDIFF(YEAR, 0, CURRENT_TIMESTAMP), 0)
AND order_date < DATEDIFF(DAY, -1, CURRENT_TIMESTAMP);
-- Get Month to Date Orders
SELECT order_date
FROM Orders
WHERE order_date >= DATEADD(MONTH,
DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0)
AND order_date < DATEDIFF(DAY, -1, CURRENT_TIMESTAMP);
-- Get Last Year's Orders
SELECT order_date
FROM Orders
WHERE order_date >= DATEADD(YEAR,
DATEDIFF(YEAR, 0, CURRENT_TIMESTAMP)-1, 0)
AND order_date < DATEADD(YEAR,
DATEDIFF(YEAR, 0, CURRENT_TIMESTAMP), 0);
-- Get Today's Orders
SELECT order_date
FROM Orders
WHERE order_date >= DATEDIFF(DAY, 0, CURRENT_TIMESTAMP)
AND order_date < DATEDIFF(DAY, -1, CURRENT_TIMESTAMP);
-- Get Yesterday's Orders
SELECT order_date
FROM Orders
WHERE order_date >= DATEDIFF(DAY, 1, CURRENT_TIMESTAMP)
AND order_date < DATEDIFF(DAY, 0, CURRENT_TIMESTAMP);
-- Get Today's Orders Between 9:00 am And 11:00 am
SELECT order_date
FROM Orders
WHERE order_date BETWEEN
CONVERT(VARCHAR(10), CURRENT_TIMESTAMP, 101) + ' 09:00:00.000'
AND CONVERT(VARCHAR(10), CURRENT_TIMESTAMP, 101) + ' 11:00:00.000';
-- Or
SELECT order_date
FROM Orders
WHERE order_date BETWEEN
DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), '09:00:00')
AND DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), '11:00:00');
-- Get Orders for Last Week
SELECT order_date
FROM Orders
WHERE order_date >= DATEADD(WEEK,
DATEDIFF(WEEK, 0, CURRENT_TIMESTAMP) - 1, 0)
AND order_date < DATEADD(WEEK,
DATEDIFF(WEEK, 0, CURRENT_TIMESTAMP), 0);
-- Get Orders for Current Week
SELECT order_date
FROM Orders
WHERE order_date >= DATEADD(WEEK,
DATEDIFF(WEEK, 0, CURRENT_TIMESTAMP), 0)
AND order_date < DATEADD(WEEK,
DATEDIFF(WEEK, 0, CURRENT_TIMESTAMP) + 1, 0);
-- Get Orders for One Year Back From Current Date
SELECT order_date
FROM Orders
WHERE order_date >= DATEDIFF(DAY, 0,
DATEADD(YEAR, -1, CURRENT_TIMESTAMP))
AND order_date < DATEDIFF(DAY, -1, CURRENT_TIMESTAMP);
-- Trim time portion
DECLARE @date DATETIME;
SET @date = CURRENT_TIMESTAMP;
-- Convertmg to string and back to date
SELECT CONVERT(DATETIME, CONVERT(CHAR(10), @date, 101)) ;
-- Using datetime functions
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, @date),0);
-- Format Time From 5.25 to 5:15
-- Decimal Format to Time Format
DECLARE @time DECIMAL(5, 2);
SET @time = 5.25;
SELECT CONVERT(CHAR(5), DATEADD(ss, @time * 3600, 0), 108);
Labels: datetime, sql server, t-sql programming