This is a date conversion reference that I've use for personal reference. Simply copy-paste the code into your Microsoft SSMS application.
I hope you will find it useful. :)
Sample Result:
Code:
/*--------------------------------------------------------------------------------------------------------------------------------------
Report Name: Date Conversion Reference
Created By: LearnwithGlezy.com
For: Microsoft SSMS
Warning: Use at your own risk! Always test results!
DATEDIFF() function returns the difference between two dates. DATEDIFF(interval, date1, date2)
DATEADD() function adds a time/date interval to a date and then returns the date. DATEADD(interval, number, date)
DATENAME() function returns a specified part of a date. DATENAME(interval, date)
Intervals:
year, yyyy, yy = Year
quarter, qq, q = Quarter
month, mm, m = month
dayofyear = Day of the year
day, dy, y = Day
week, ww, wk = Week
weekday, dw, w = Weekday
hour, hh = hour
minute, mi, n = Minute
second, ss, s = Second
millisecond, ms = Millisecond
Study guides:
--------------------------------------------------------------------------------------------------------------------------------------*/
DROP TABLE IF EXISTS #DateTime
CREATE TABLE #DateTime([Description] VARCHAR(MAX),Formula VARCHAR(MAX),Result Datetime)
DROP TABLE IF EXISTS #Varchar
CREATE TABLE #Varchar([Description] VARCHAR(MAX),Formula VARCHAR(MAX),Result VARCHAR(255))
DROP TABLE IF EXISTS #Int
CREATE TABLE #Int([Description] VARCHAR(MAX),Formula VARCHAR(MAX),Result Int)
--------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO #DateTime ([Description], Formula, Result) VALUES
('Obtain Today','SELECT GETDATE()',(SELECT GETDATE()))
, ('Obtain 1st day of prior month','SELECT DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) - 2, 0)', (SELECT DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) - 2, 0)))
, ('Obtain Last day of prior month' ,'SELECT DATEADD(SS, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))',(SELECT DATEADD(SS, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))))
, ('Obtain 1st day of the year','SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)',(SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)))
, ('Obtain Last day of the year','SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, -1)',(SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, -1)))
, ('Obtain 1st day of the following year','SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0) ',(SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0)))
, ('Obtain Last day of the year up to last second','SELECT DATEADD(MILLISECOND, -3, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0))',(SELECT DATEADD(MILLISECOND, -3, DATEADD(YY, DATEDIFF(YY, 0, GETDATE()) + 1, 0))))
, ('Obtain Last biz day if date reference table is not available','SELECT DATEADD(DAY, CASE (DATEPART(WEEKDAY, GETDATE()) + @@DATEFIRST) % 7 WHEN 1 THEN -2 WHEN 2 THEN -3 ELSE -1 END, DATEDIFF(DAY, 0, GETDATE()))',(SELECT DATEADD(DAY, CASE (DATEPART(WEEKDAY, GETDATE()) + @@DATEFIRST) % 7 WHEN 1 THEN -2 WHEN 2 THEN -3 ELSE -1 END, DATEDIFF(DAY, 0, GETDATE()))))
--------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO #Varchar ([Description], Formula, Result) VALUES
('Obtain Name' ,'SELECT DATENAME(MONTH, GETDATE())' ,(SELECT DATENAME(MONTH, GETDATE())))
, ('Obtain Day' ,'SELECT DATENAME(DAY, GETDATE())' ,(SELECT DATENAME(DAY, GETDATE())))
, ('Obtain Year' ,'SELECT DATENAME(YEAR, GETDATE())' ,(SELECT DATENAME(YEAR, GETDATE())))
, ('Obtain Quarter' ,'SELECT DATENAME(QUARTER, GETDATE())' ,(SELECT DATENAME(QUARTER, GETDATE())))
, ('Obtain Hour' ,'SELECT DATENAME(HOUR, GETDATE())' ,(SELECT DATENAME(HOUR, GETDATE())))
, ('Obtain Minute' ,'SELECT DATENAME(MINUTE, GETDATE())' ,(SELECT DATENAME(MINUTE, GETDATE())))
, ('Obtain Second' ,'SELECT DATENAME(SECOND, GETDATE())' ,(SELECT DATENAME(SECOND, GETDATE())))
, ('Obtain MilliSecond' ,'SELECT DATENAME(MILLISECOND, GETDATE())',(SELECT DATENAME(MILLISECOND, GETDATE())))
, ('Convert to MONTH DD YYYY HH:MM AM/PM','SELECT CONVERT(VARCHAR, GETDATE(), 0)',(SELECT CONVERT(VARCHAR, GETDATE(), 0)))
, ('Convert to MM/DD/YY' ,'SELECT CONVERT(VARCHAR, GETDATE(), 1)',(SELECT CONVERT(VARCHAR, GETDATE(), 1)))
, ('Convert to YY.MM.DD' ,'SELECT CONVERT(VARCHAR, GETDATE(), 2)',(SELECT CONVERT(VARCHAR, GETDATE(), 2)))
, ('Convert to DD/MM/YY' ,'SELECT CONVERT(VARCHAR, GETDATE(), 3)',(SELECT CONVERT(VARCHAR, GETDATE(), 3)))
, ('Convert to DD.MM.YY' ,'SELECT CONVERT(VARCHAR, GETDATE(), 4)',(SELECT CONVERT(VARCHAR, GETDATE(), 4)))
, ('Convert to DD-MM-YY' ,'SELECT CONVERT(VARCHAR, GETDATE(), 5)',(SELECT CONVERT(VARCHAR, GETDATE(), 5)))
, ('Convert to DD-MONTH-YY' ,'SELECT CONVERT(VARCHAR, GETDATE(), 6)',(SELECT CONVERT(VARCHAR, GETDATE(), 6)))
, ('Convert to MONTH DD, YY' ,'SELECT CONVERT(VARCHAR, GETDATE(), 7)',(SELECT CONVERT(VARCHAR, GETDATE(), 7)))
, ('Convert to HH:MM:SS','SELECT CONVERT(VARCHAR, GETDATE(), 8)' ,(SELECT CONVERT(VARCHAR, GETDATE(), 8)))
, ('Convert to MONTH DD YYYY HH:MM:SS:NNN AM/PM','SELECT CONVERT(VARCHAR, GETDATE(), 9)',(SELECT CONVERT(VARCHAR, GETDATE(), 9)))
, ('Convert to MM-DD-YY','SELECT CONVERT(VARCHAR, GETDATE(), 10)' ,(SELECT CONVERT(VARCHAR, GETDATE(), 10)))
, ('Convert to YY/MM/DD','SELECT CONVERT(VARCHAR, GETDATE(), 11)' ,(SELECT CONVERT(VARCHAR, GETDATE(), 11)))
, ('Convert to YYMMDD','SELECT CONVERT(VARCHAR, GETDATE(), 12)' ,(SELECT CONVERT(VARCHAR, GETDATE(), 12)))
, ('Convert to DD MONTH YYYY HH:MM:SS:NNN AM/PM','SELECT CONVERT(VARCHAR, GETDATE(), 13)',(SELECT CONVERT(VARCHAR, GETDATE(), 13)))
, ('Convert to HH:MM:SS:NNN','SELECT CONVERT(VARCHAR, GETDATE(), 14)',(SELECT CONVERT(VARCHAR, GETDATE(), 14)))
, ('Convert to YYYY-MM-DD HH:MM:SS','SELECT CONVERT(VARCHAR, GETDATE(), 20)',(SELECT CONVERT(VARCHAR, GETDATE(), 20)))
, ('Convert to YYYY-MM-DD HH:MM:SS:NNN','SELECT CONVERT(VARCHAR, GETDATE(), 21)',(SELECT CONVERT(VARCHAR, GETDATE(), 21)))
, ('Convert to MM/DD/YY HH:MM:SS AM/PM','SELECT CONVERT(VARCHAR, GETDATE(), 22)',(SELECT CONVERT(VARCHAR, GETDATE(), 22)))
, ('Convert to YYYY-MM-DD','SELECT CONVERT(VARCHAR, GETDATE(), 23)',(SELECT CONVERT(VARCHAR, GETDATE(), 23)))
, ('Convert to HH:MM:SS','SELECT CONVERT(VARCHAR, GETDATE(), 24)',(SELECT CONVERT(VARCHAR, GETDATE(), 24)))
, ('Convert to YYYY-MM-DD HH:MM:SS:NNN','SELECT CONVERT(VARCHAR, GETDATE(), 25)',(SELECT CONVERT(VARCHAR, GETDATE(), 25)))
, ('Convert to MONTH DD YYYY HH:MM AM/PM','SELECT CONVERT(VARCHAR, GETDATE(), 100)',(SELECT CONVERT(VARCHAR, GETDATE(), 100)))
, ('Convert to MM/DD/YYYY','SELECT CONVERT(VARCHAR, GETDATE(), 101)' ,(SELECT CONVERT(VARCHAR, GETDATE(), 101)))
, ('Convert to YYYY.MM.DD','SELECT CONVERT(VARCHAR, GETDATE(), 102)' ,(SELECT CONVERT(VARCHAR, GETDATE(), 102)))
, ('Convert to DD/MM/YYYY','SELECT CONVERT(VARCHAR, GETDATE(), 103)' ,(SELECT CONVERT(VARCHAR, GETDATE(), 103)))
, ('Convert to DD.MM.YYYY','SELECT CONVERT(VARCHAR, GETDATE(), 104)' ,(SELECT CONVERT(VARCHAR, GETDATE(), 104)))
, ('Convert to DD-MM-YYYY','SELECT CONVERT(VARCHAR, GETDATE(), 105)' ,(SELECT CONVERT(VARCHAR, GETDATE(), 105)))
, ('Convert to DD MONTH YYYY','SELECT CONVERT(VARCHAR, GETDATE(), 106)' ,(SELECT CONVERT(VARCHAR, GETDATE(), 106)))
, ('Convert to MONTH DD, YYYY','SELECT CONVERT(VARCHAR, GETDATE(), 107)' ,(SELECT CONVERT(VARCHAR, GETDATE(), 107)))
, ('Convert to HH:MM:SS','SELECT CONVERT(VARCHAR, GETDATE(), 108)' ,(SELECT CONVERT(VARCHAR, GETDATE(), 108)))
, ('Convert to MONTH DD YYYY HH:MM:SS:NNN AM/PM','SELECT CONVERT(VARCHAR, GETDATE(), 109)',(SELECT CONVERT(VARCHAR, GETDATE(), 109)))
, ('Convert to MM-DD-YYYY','SELECT CONVERT(VARCHAR, GETDATE(), 110)' ,(SELECT CONVERT(VARCHAR, GETDATE(), 110)))
, ('Convert to YYYY/MM/DD','SELECT CONVERT(VARCHAR, GETDATE(), 111)' ,(SELECT CONVERT(VARCHAR, GETDATE(), 111)))
, ('Convert to YYYYMMDD','SELECT CONVERT(VARCHAR, GETDATE(), 112)' ,(SELECT CONVERT(VARCHAR, GETDATE(), 112)))
, ('Convert to DD MONTH YYYY HH:MM:SS:NNN','SELECT CONVERT(VARCHAR, GETDATE(), 113)',(SELECT CONVERT(VARCHAR, GETDATE(), 113)))
, ('Convert to HH:MM:SS:NNN','SELECT CONVERT(VARCHAR, GETDATE(), 114)' ,(SELECT CONVERT(VARCHAR, GETDATE(), 114)))
, ('Convert to YYYY-MM-DD HH:MM:SS','SELECT CONVERT(VARCHAR, GETDATE(), 120)' ,(SELECT CONVERT(VARCHAR, GETDATE(), 120)))
, ('Convert to YYYY-MM-DD HH:MM:SS:NNN','SELECT CONVERT(VARCHAR, GETDATE(), 121)' ,(SELECT CONVERT(VARCHAR, GETDATE(), 121)))
, ('Convert to YYYY-MM-DD T HH:MM:SS:NNN','SELECT CONVERT(VARCHAR, GETDATE(), 126)' ,(SELECT CONVERT(VARCHAR, GETDATE(), 126)))
, ('Convert to YYYY-MM-DD T HH:MM:SS:NNN','SELECT CONVERT(VARCHAR, GETDATE(), 127)' ,(SELECT CONVERT(VARCHAR, GETDATE(), 127)))
--------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO #Int ([Description], Formula, Result) VALUES
('Calculate Days between two dates' ,'SELECT DATEDIFF(DAY,''01/01/2022'',''12/31/2022'')',(SELECT DATEDIFF(DAY,'01/01/2022','12/31/2022')))
, ('Calculate Months between two dates' ,'SELECT DATEDIFF(MONTH,''01/01/2022'',''12/31/2022'')',(SELECT DATEDIFF(MONTH,'01/01/2022','12/31/2022')))
, ('Calculate Weeks between two dates' ,'SELECT DATEDIFF(WEEK,''01/01/2022'',''12/31/2022'')',(SELECT DATEDIFF(WEEK,'01/01/2022','12/31/2022')))
--------------------------------------------------------------------------------------------------------------------------------------
SELECT DISTINCT x.[Description], x.Formula, Date_Time_Result = dt.result, Varchar_Result = vc.result, Int_Result = ic.result FROM
(SELECT [Description], Formula FROM #DateTime
UNION
SELECT [Description], Formula FROM #Varchar
UNION
SELECT [Description], Formula FROM #Int) x
LEFT JOIN #Int ic on x.Description = ic.Description
LEFT JOIN #DateTime dt ON x.Description = dt.Description
LEFT JOIN #Varchar vc ON x.Description = vc.Description
ORDER BY 1
DROP TABLE IF EXISTS #DateTime
DROP TABLE IF EXISTS #Varchar
DROP TABLE IF EXISTS #Int
Commentaires