My quick reference for TSQL date/time formats that I use often.
More information may be found at: https://learn.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql?view=sql-server-ver16
| PART | FORMAT | TSQL CODE | NOTE | OUTPUT |
| PARSE | parse | SELECT PARSE(’02/12/2012′ AS datetime USING ‘de-DE’); | 2012-12-02 00:00:00.000 | |
| try_parse | SELECT TRY_PARSE(’02/12/20122′ AS datetime USING ‘en-US’); | NULL | ||
| First Day of Week | SELECT DATEADD(wk, DATEDIFF(wk, 6, CURRENT_TIMESTAMP), 6); | current | 2023-11-12 00:00:00.000 | |
| Last Day of Week | SELECT DATEADD(wk, DATEDIFF(wk, 6, CURRENT_TIMESTAMP), 6 + 6); | current | 2023-11-18 00:00:00.000 | |
| First Day of Month | SELECT DATEADD(DAY,1,EOMONTH(getdate(),-1)) | current | 2023-11-01 | |
| SELECT DATEADD(DAY,1,EOMONTH(getdate(),-2)) | last month | 2023-10-01 | ||
| SELECT DATEADD(DAY,1,EOMONTH(getdate(),0)) | next month | 2023-12-01 | ||
| Last Day of Month | SELECT EOMONTH(getdate()) | current | 2023-11-30 | |
| SELECT EOMONTH(getdate(), -1) | last month | 2023-10-31 | ||
| SELECT EOMONTH(getdate(), 1) | next month | 2023-12-31 | ||
| Week Day Name | SELECT DATENAME(dw,GETDATE()); | Tuesday | ||
| Full Text Date | SELECT FORMAT (getdate(), ‘dddd, MMMM dd, yyyy’); | English | Tuesday, November 14, 2023 | |
| SELECT FORMAT (getdate(), ‘dddd, MMMM dd, yyyy’,’es-es’); | Spanish | martes, noviembre 14, 2023 | ||
| Date Formats | yyyyMMdd | SELECT FORMAT (getdate(), ‘yyyyMMdd HHmmss’); | with time | 20231114 085329 |
| SELECT FORMAT (getdate(), ‘yyyyMMdd’); | 20231114 | |||
| SELECT CONVERT(VARCHAR(10), GETDATE(),112); | 20231114 | |||
| MM/dd/yyyy | SELECT FORMAT (getdate(), ‘d’,’us’); | 11/14/2023 | ||
| SELECT FORMAT (getdate(), ‘MM/dd/yyyy’); | 11/14/2023 | |||
| SELECT FORMAT (getdate(), ‘hh:mm:ss tt’); | 08:54:37 AM | |||
| SELECT FORMAT (getdate(), ‘MM/dd/yyyy hh:mm:ss tt’); | with time | 11/14/2023 08:54:37 AM | ||
| yyyy:MM:dd HH:mm | SELECT FORMAT(GETDATE(), ‘yyyy:MM:dd HH:mm’); | with time | 2023:11:14 08:57 | |
| yyyy-MM-dd | SELECT FORMAT (getdate(), ‘yyyy-MM-dd’); | 2023-11-14 | ||
| SELECT CONVERT(VARCHAR(10), GETDATE(),120); | 2023-11-14 | |||
| Reversed Date Formats | MM/dd/yyyy | SELECT CONVERT(NVARCHAR(12),(convert(date,CONVERT(CHAR(10), 20221101), 101)),101); | 11/01/2022 | |
| yyyy-MM-dd | SELECT CAST(CAST(20221101 AS VARCHAR(50)) AS DATE) | 2022-11-01 | ||
| Time Formats | HHmmss | SELECT FORMAT (getdate(), ‘HHmmss’); | 090122 | |
| SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(),108),’:’,”); | 090122 | |||
| hh:mm:ss | SELECT FORMAT(SYSDATETIME(), N’hh:mm:ss tt’); | 09:01:22 AM | ||
| SELECT FORMAT(SYSDATETIME(), N’hh:mm t’); | 09:01 A | |||
| AGE | DECLARE @DOB AS date = ‘1955-11-05’ SELECT (CONVERT(int,CONVERT(char(8),GETDATE(),112))-CONVERT(char(8),@Dob,112))/10000; | 68 | ||