Chapter 7 – Date Function
"An inch of time cannot be bought with an inch of gold."
- Chinese Proverb
Current_Timestamp
Above is the keyword Current_Timestamp that allows a user to get the timestamp. This is a reserved word and so the system will deliver the timestamp to you when requested.
Getdate
This example uses the Getdate() function to return the timestamp.
SELECT Getdate() as "The Date";
The Date
------------
03/30/2015 8:46:04.567
“Speak in a moment of anger and you’ll deliver the greatest speech you’ll ever regret.”
– Anonymous
The Getdate command will return today’s date and time just like the Current_Timestamp command. This is not ANSI.
Date and Time Keywords
SELECT
GETDATE()AS [GETDATE]
, CURRENT_TIMESTAMP AS [CURRENT_TIMESTAMP]
, GETUTCDATE()AS [GETUTCDATE]
SELECT
SYSDATETIME()AS [SYSDATETIME]
,SYSUTCDATETIME()AS [SYSUTCDATETIME]
The above examples show how to get the date and time. The GETDATE and CURRENT_TIMESTAMP are equivalent, but CURRENT_TIMESTAMP is ANSI compliant. The differences between the top and bottom examples are that the top has a data type of DATETIME and the bottom DATETIME2, which is an expanded form of DATETIME.
SYSDATETIMEOFFSET Provides the Timezone Offset
SELECT
SYSUTCDATETIME()AS [SYSUTCDATETIME]
,SYSDATETIMEOFFSET()AS [SYSDATETIMEOFFSET];
“Life is a succession of lessons, which must be lived to be understood”.
--Ralph Waldo Emerson
The CETUTCDATE function will provide a Current_Timestamp, but in Universal Time Coordinate (UTC) time. The SYSDATETIMEOFFSET shows the timezone difference between UTC and the local Current_Timestamp. Remember, date and time are a succession of days and hours, which must be queried to be understood!
SYSDATETIMEOFFSET Provides the Timezone Offset
This is how you can get just the current_date and the current_time . .
Using both CAST and CONVERT in Literal Values
SELECT CAST('20150216' AS DATE) as "Date YMD";
Date YMD
2015-02-16
SELECT CONVERT(CHAR(8), CURRENT_TIMESTAMP, 112)
AS "Converted" ;
Converted
20150330
This converts the current date and time to CHAR(8) by using style 112 ('YYYYMMDD')
This is an example of using the CAST function with a date literal. The first SQL example converts the character string literal ‘20150216’ to a DATE data type. The second SQL example converts the current date and current time to a CHAR (8) data type using the style 112, which is a 'YYYYMMDD' format.
Using Both CAST and CONVERT in Literal Values
This example converts the current date and time value to CHAR (12) by using style 114 ('hh:mm:ss.nnn').
Using both CAST and CONVERT in Literal Values
SELECT
SYSDATETIME() as "Local Time Eastern"
,SWITCHOFFSET(SYSDATETIMEOFFSET(), '-06:00')
as "Timestamp Central"
,SWITCHOFFSET(SYSDATETIMEOFFSET(), '-07:00')
as "Timestamp Mountain"
,SWITCHOFFSET(SYSDATETIMEOFFSET(), '-08:00')
as "Timestamp Pacific" ;
2015-03-30 11:03:38.9877064Local Time Eastern
2015-03-30 10:03:38.9877064 -06:00Timestamp Central
2015-03-30 09:03:38.9877064 -07:00Timestamp Mountain
2015-03-30 08:03:38.9877064 -08:00Timestamp Pacific
The times above are the converted times, but they are displayed vertically to save space on the screen
The SWITCHOFFSET function can be used to adjust an input DATETIMEOFFSET value to a specified time zone. We are showing in the example SQL above how to convert to Central, Mountain and Pacific time.
The DATEADD Function
Valid values for the part input include year, quarter, month, dayofyear, day, week, weekday, hour, minute, second, millisecond, microsecond, and nanosecond. You can also specify the part in abbreviated form, such as yy instead of year.
The syntax for the DATEADD function is DATEADD (part, n, date_value). Valid values for the part are year, quarter, month, dayofyear, day, week, weekday, hour, minute, second, millisecond, microsecond, and nanosecond.
The DATEDIFF Function
The syntax for the DATEDIFF function is DATEDIFF (part, dt_val1, dt_val2). Above we have used the literal dates of '2014-01-30 (January 30, 2014) and '2015-06-30' (June 30, 2015). We then can see the differences in the number of years, months, days, hours, minutes and seconds.
DATEADD Function
SELECT Order_Date
,DateAdd (Day, 60,Order_Date) as "Due Date"
,Order_Total
,DateAdd (Day, 50,Order_Date) as Discount
,Cast(Order_Total *.98 as Decimal(8,2)) as Discount_Total
FROMOrder_Table
ORDER BY 1 ;
Valid values for the part argument include year, quarter, month, dayofyear, day, week, weekday, hour, minute, second, millisecond, microsecond, nanosecond, TZoffset, and ISO_WEEK.
A Real World Example for DateAdd Using the Order Table
SELECT Order_Date
,DateAdd (Day, 60,Order_Date) as "Due Date"
,Order_Total
,DateAdd (Day, 50,Order_Date) as Discount
,Cast(Order_Total *.98 as Decimal(8,2)) as Discount_Total
FROMOrder_Table
ORDER BY 1 ;
The example above uses a real world example from the Order_Table.
DATEPART Function
SELECT Order_Date
,DateAdd (Day, 60,Order_Date) as "Due Date"
,Order_Total
,DateAdd (Day, 50,Order_Date) as Discount
,Cast(Order_Total *.98 as Decimal(8,2)) as Discount_Total
FROMOrder_Table
WHERE DATEPART(Month, Order_Date) = 10
ORDER BY 1 ;
This example only looks for orders that happened in October. This is done by using the DATEPART function in the WHERE clause. Valid values for the part argument include year, quarter, month, dayofyear, day, week, weekday, hour, minute, second, millisecond, microsecond, nanosecond, TZoffset, and ISO_WEEK.
DATEPART Function Examples
Above are some excellent examples to pull from using the DATEPART function.
YEAR, MONTH, and DAY Functions
SELECT Order_Date
,Year(Order_Date)as "Yr"
,Month(Order_Date) as "Mo"
,Day(Order_Date)as "Day"
FROMOrder_Table
ORDER BY 1 ;
The YEAR, MONTH, and DAY functions are abbreviations for the DATEPART function.
A Better Technique for YEAR, MONTH, and DAY Functions
Both queries above do the same thing and deliver the same result set, but the bottom query could be much faster.
Above are the tale of two queries. The top query applies manipulation on the filtered column, in most cases SQL Server can’t use an index efficiently when using this technique. The bottom query uses a range filter instead. Brilliant!
DATENAME Function
SELECT Order_Date
,DATENAME(Year, Order_Date) as "Yr"
,DATENAME(Month, Order_Date) as "Mo"
,DATENAME(Day, Order_Date) as "Day"
FROM Order_Table
ORDER BY 1 ;
The DATENAME function returns the name of the requested part rather than the number. Notice above that only the Month returns the actual name of the month, but both the Year and the Day still return the integer values.
Date Formatting
SELECT
GetDate() as [default]
,CONVERT(VARCHAR(20), GETDATE(), 100) [Month AM PM]
,CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY]
,CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]
,CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD]
SELECT
CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD]
,CONVERT(VARCHAR(26), GETDATE(), 109) AS [Month Time 3 Mil]
,DATENAME(MM, GETDATE())
+ RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) AS [Month DD, YYYY]
,REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ' ', '-') AS [DD-Mon-YYYY]
How about those examples!
Time Formatting
SELECT
substring(convert(varchar(20), GetDate(), 9), 13, 5) + ' '
+ substring(convert(varchar(30), GetDate(), 9), 25, 2) As [time AM PM]
,convert(varchar, getdate(), 108) [hh:mm:ss]
,convert(varchar, getdate(), 109) [mon dd yyyy hh:mm:ss:mmmAM];
SELECT
convert(varchar, getdate(), 121) [yyyy-mm-dd hh:mm:ss.mmm]
,convert(varchar, getdate(), 126) [yyyy-mm-ddThh:mm:ss.mmm]
,convert(varchar, getdate(), 114) [hh:mm:ss:mmm(24h)] ;
How about those examples! What great timing!
ISDATE Function
The ISDATE function accepts a character string as input and returns a Boolean. ISDATE returns a 1 if it is convertible to a date and time data type. It returns a 0 if it is not convertible to a date and time data type. Above, we have used the date of February 29th. This is only a valid date during a leap year. It only returns a 1 when the date is valid.