Magic with datetime functions(MS_SQL_SERVET)
Its often easy to get a practical approach to understand any concepts that you wan to get familiar with. In process of learning SQL querying, datetime is often most required as to generate report for specific date or time etc. To get them in better way, I think of practical cases. For this, case is to find what is the 1st day of the year based on given date.
The code is in my GitHub repo. I have used Datepart(), Dateadd(), Datename()and getdate() functions to get the result.
Before going into detail of the the stored procedure and above functions lets see what will be the output. I have attached calendar of Jan 2021 to confirm the same.
Lets look at individual function before diving into the stored_procedure.
Note: This stored proc does not require any database to query, we can use w3school tryit editor, which I have used here. however, I have used the database provided in w3school to briefly explain the functions.
1 Getdate(): This returns current datetime depending on current time-zone you are in. The output is in format -‘yyyy-mm-dddd:hh:mm;ss.ns’. This function takes no input.
eg as if you need to see list of orders that has been ordered till today. as expected, the query lists all the order which were ordered before today’s date.(kindly note snapshot above for today’s date)
All the below functions require one common parameter called as interval or part. that will specify which part of date we are interested in. This interval or part accepts one of the input as given here.
datepart( part_of_date, The_date): provide int output of the date. with we can get what is the year , month, day, day of week, day or year, week of year etc from the given input date.
as example if we need to find list of all the orders that were ordered before in Dec 1996, they we can use this function to extract the year part of the order date and then compare it with our condition. we will do same with month part.
as you can see in above result, only those orders are selected which were ordered in Dec 1996 ordered in desc order.
one more example, to select all the order placed in 1st quarter of 1996.
As expected, only months of 1st quarter are selected.
we can same for day, day of week, day of year, hr, mm, sec etc and get desired result.
DateName( part_of_date, The_date): This is same as above function just it provide string output for the part of date in contrast to int output of datepart().
lets look at the difference in output for both. except for month part, output of both Datepart() and Datename is totally same. as in below, if we use datename to get month of ‘2020–4–4’ then we get April and for Datepart(), we get 4. Rest for any other part output will be same.
Dateadd( part_of_date, (+/-) int_value, The_date): This function is used to add or substract certain int value from part of the given date.
Take a scenario as if you want to find orders placed 5 or 6 days or months or years or time before the current date, each time we can input the date or just substract certain part form today’s date.
As we can see, we substracted and added 5 in current date. observe, it automatically adjusted the month when we substracted 5 from the date.
And Now the final combined usage of all the above briefed datetime function.
we have declared a variable, gave it a value and then used our above specified datetime functions to get the result. Lets see the query part by part.
Assuming date as in previous screenshot as 2021–03–06,
- Retrun date value of the imputDate which is 6 and then add -(minus ) sign.
- Above result (i.e. 6) add 1 (-6+1) and then add this value to day part of InputDate and return a date which is 2021–03–01 00:00:00.000
- As step 1, step 3 returns month part of the InputDate which is 3 and then add -(minus ) sign.
- Result of step three is taken input and 1 is added to it resulting to -2 and it is added to month value of InputDate. Returning date as 2021–01–01 00:00:00.000
- as you can see month and day value is 1 now. This final value is passed to DateName() function and it returns the string presentation of 1st day of the year.
This is how we can find what is/was/will be first day of year for any given date.
