I work with Date/Time functions quite often for one of my clients. Today I thought to myself, “hey, how many days until my wedding date of 12/31/2023?”. So, why not write something showcasing some of the different date functions and incorporate that special day in with it. So, without further ado, let’s get right into it.
Let’s start off with something basic and progressively add to the complexity. Let’s begin by getting the Current Date Time. To do so, this is the code that you would need to write…
SELECT GETDATE() AS CurrentDateTime
For me this returned:
CurrentDateTime
2023-11-30 12:16:01.473
This is the most basic date function that you could possibly do. Next off we are going to use the CONVERT function to get the Current Date in YYYY-MM-DD format. To do so you would write this…
SELECT CONVERT(DATE,GETDATE()) AS CurrentDate
For me this returned:
CurrentDate
2023-11-30
Now we are going to level up a bit in terms of complexity. For this next script we are going to use the DATEADD function to return the same date of one month ago.
SELECT CONVERT(DATE,DATEADD(MONTH, -1, GETDATE())) AS OneMonthAgo
For me this returned:
OneMonthAgo
2023-10-30
How about we go the other direction? For this script we are going to try and return the date 1 month from now also using the DATEADD function. If you thought that we would just take the “-1” out from the last script and replace it with a “1” then you would be correct.
SELECT CONVERT(DATE,DATEADD(month, 1, GETDATE())) AS NextMonth
For me this returned…
NextMonth
2023-12-30
Now for a fun one; the DATEDIFF function. Let’s say that you want to have a countdown that tells you how many days there are until a specified date. “How would you do that” you may ask? Look no further, I will show you by using my wedding date. The script would look like this…
SELECT DATEDIFF(day, CONVERT(DATE, GETDATE()), ‘2023/12/31’) AS DaysUntilWedding
For me this returned:
DaysUntilWedding
31
You can also pass in variables to get the desired results. First up we are going to try and find what the first day of last month is. To do so, this is the script that you would need to write.
DECLARE @MonthsBack TINYINT = 2
SELECT DATEADD(day, 1, DATEADD(month, -@MonthsBack, EOMONTH(GETDATE()))) AS FirstDayLastMonth
For me this returned…
FirstDayLastMonth
2023-10-01
For my second example I am going to do a similar script to the previous “DaysUntilWedding” except I will pass in the date using a variable.
DECLARE @WeddingDate DATE = ‘2023-12-31’
SELECT DATEDIFF (day, GETDATE(), @WeddingDate) AS DaysUntilWedding
GO
For me this returned…
DaysUntilWedding
31
That will be all for my Practicing Date Functions blog. I hope that it was informative and that you learned something. Have a great rest of your day! Thank you for reading.
Bailey McDonald
Data Engineer, Patriot Consulting
Email: bkmcdonald@patriotconsultingcorp.com | Blogs: Patriot Consulting Blogs
LinkedIn: Personal: BaileyMcDonald | Company: Patriot Consulting
