Introduction to SQL Datetime functions:
.jpg "Sql DateTime Function")
- GETDATE()
- CURRENT_TIMESTAMP
- DATEPART()
- DATENAME()
- EOMONTH()
- DATEADD()
- DATEDIFF()
- FORMAT()
1. GETDATE function in SQL:
Our journey begins with understanding the current time of server (on which SQL instance is running from). One of the ways to achieve it is by using the GETDATE() function.
SELECT GETDATE() AS Current_Datetime;
Here, In result we get today’s date along with current time till milliseconds.
Similar result can be achieved using built in function called Current_Timestamp
SELECT CURRENT_TIMESTAMP AS CurrentTimestamp;
O/P:
2. DATEPART and DATENAME function in SQL:
The DATEPART function in SQL Server is used to extract specific parts (such as year, month, day, hour, minute, etc.) from a given date or time value. It's commonly used to break down a datetime value into its individual components. Unlike DATEPART(), DATETIME() function returns character values for extracted element.
Here are some examples of using the DATEPART() and DATENAME() functions:
a. Extracting Month:
SELECT DATEPART(MONTH, '2023-08-17') AS Extracted_Month
O/P:
Here, we get numeric value 8 as result which refers to current month of a calendar year.
SELECT DATENAME(MONTH, '2023-08-17') AS Extracted_Month
O/P:
Here, we get string value 'August' as rsult. This is basic difference between DATEPART() and DATENAME() function.
b. Extracting Quarter:
SELECT DATEPART(QUARTER, '2023-08-22') AS Extracted_Quarter
O/P:
SELECT DATENAME(QUARTER, '2023-08-22') AS Extracted_Quarter
O/P:
Unlike MONTH, While extracting QUARTER from both DATEPART() and DATENAME() we get similar result. We do not get ‘Quarter 3’ or ‘Q3’ in result in case of DATENAME().
c. Extracting Year:
SELECT DATEPART(YEAR, '2023-08-22') AS Extracted_Year
O/P:
SELECT DATENAME(YEAR, '2023-08-22') AS Extracted_Year
O/P:
Here, Like 'Quarter' we get similar results while extracting yar from both DATEPART() and DATENAME() functions. Thre's no in-built function that returns 2023 as 'Two Thousand Twenty Three'.
d. Extracting Week:
SELECT DATEPART(WEEK, '2023-08-17') AS Extracted_WeekNumber
O/P:
Here, We get week number of the year. In our cae it is 33. To extract week in string format we can use following query
SELECT DATENAME(WEEKDAY, '2023-08-22') AS Extracted_WeekDay
SELECT DATENAME(DW, '2023-08-22') AS Extracted_WeekDay
O/P:
e. Extracting Day:
SELECT DATEPART(DAY, '2023-08-22') AS extracted_day
,DATEPART(DAYOFYEAR, '2023-08-22') AS extracted_Day_Of_Year
,DATEPART(DY, '2023-08-22') AS extracted_Day_Of_Year
O/P:
Here, We are using DATEPART() function thrice on same date with different Keyword.
1st column has DAY keyword which
returns 22 as day of the month.
2nd column has DAYOFTHEYEAR keyword
which returns 234 as day of the year.
3rd column has DY keyword which
returns 234 as day of the year
Let’s see what we can achieve with DATENAME() function.
SELECT DATENAME(DAY, '2023-08-22') AS extracted_day
,DATENAME(DAYOFYEAR, '2023-08-22') AS extracted_Day_Of_Year
,DATENAME(DY, '2023-08-22') AS extracted_Day_Of_Year
O/P:
As we can see, in this case DATENAME() produces similar results.
f. Extracting Hour:
SELECT DATEPART(HOUR, '2023-08-22 11:30:42') AS Extracted_Hour
O/P:
Here, In result we get 11 as number which refers to 11th hour in of the day. We get same result with DATENAME() function.
g. Extracting Minute:
SELECT DATEPART(MINUTE, '2023-08-22 11:30:42') AS extracted_minute
O/P:
Here, In result we get 30 as number which refers to 30th minute of 11th hour of the day.
h. Extracting Second:
SELECT DATEPART(SECOND, '2023-08-22 11:30:42') AS extracted_second
O/P:
Here, In result we get 42 as number which refers to 42nd second of 30th minute of 11th hour of the day.
Let’s put everything together.
declare @Today datetime=getdate()
SELECT @today as
CurrentDateTime
, DATEPART(YEAR, @today) AS Current_year
, DATEPART(QUARTER, @today) AS Current_Quarter
, DATEPART(MONTH, @today) AS Current_month
, DATEPART(WEEK, @today) AS Current_week
, DATEPART(DAY, @today) AS CurrentDate
, DATEPART(HOUR, @today) AS Current_hour
, DATEPART(MINUTE, @Today) AS Current_minute
, DATEPART(SECOND, @Today) AS Current_second
O/P:
You can use the DATEPART function in various scenarios where you need to retrieve specific components of a datetime value. It's particularly useful when you want to perform calculations or analysis based on individual parts of dates or times.
3.How to Find monthend in SQL using EOMONTH() function :
The EOMONTH function in SQL Server is used to return the last day of the month for a given date expression. It's commonly used to find the end date of a month, which can be useful for various types of calculations involving date ranges.
SELECT EOMONTH('2023-08-22') AS Current_Month_End
O/P:
SELECT EOMONTH('2023-08-22',3) AS N_Month_End
O/P:
Here, We get last date of the November
month as we have passed ‘3’ in second argument which tells SQL to refer a date
3 months from passed date.
We can go back and forth with argument being positive (+N) for future months and negative (-N) for backdated months.
Unlike EOMONTH(), There’s no specific function to find Start of the month. How to Find month start in SQL using EOMONTH() function ? here’s how.
SELECT DATEADD(DAY, 1, EOMONTH('2023-04-09', -1)) AS first_date_of_month;
O/P:
Let’s break down the query.
· EOMONTH('2023-04-09', -1) : returns value 2023-03-31 as we are passing -1 as second argument which subtracts 1 month from specified date
· DATEADD(DAY, 1, ) : adds 1 day to 2023-03-31 which then becomes 2023-04-01.
4.DATEADD() function:
We can do time Travel by Adding and Subtracting Intervals using this function
SQL Server's DATEADD function enables us to manipulate dates and times effectively.
- Adding three months to current date:
SELECT Getdate() AS OriginalDate,DATEADD(MONTH, 3, Getdate()) AS Future_date
- Subtracting 20 days from current date:
SELECT Getdate() AS OriginalDate,DATEADD(DAY, -20, Getdate()) AS past_date
You can try out different results just by replacing DAY in previous query with any one of YEAR,QUARTER,WEEK,HOUR,MINUTE or SECOND
5. SQL DateTime difference using DATEDIFF() function:
The DATEDIFF function helps us understand time spans between key events.
Let’s try to understand it using an few examples.
SELECT '2023-01-01' YearStartDate,'2023-06-05' as CurrentDate
,DATEDIFF(DAY, '2023-01-01', '2023-06-05') AS Days_since_start
O/P:
Here, it calculates the number of days that have passed between two given dates: '2023-01-01' (assumed to be the start of the year) and '2023-06-05' (assumed to be the current date).
In this output, the ‘Days_since_start’ column indicates that 156 days have passed between January 1, 2023, and June 5, 2023.
SELECT '2023-01-01' as Year_Start,'2023-06-05' as ReferenceDate
,DATEDIFF(DAY, '2023-01-01', '2023-06-05') AS Days_since_start
,DATEDIFF(DAY, '2023-06-05' , '2023-12-31') AS Days_To_Finish
O/P:
In this output, the ‘Days_since_start’ column indicates that 156 days have passed between January 1, 2023, and June 5, 2023 and ‘Days_To_Finish’ column indicates that 209 days left between June 5, 2023 and Dec 31, 2023
Let’s, explore few more things with DATEDIFF().
SELECT '2023-01-01' AS StartDate,'2024-05-06' as ReferenceDate
,DATEDIFF(DAY, '2023-01-01', '2024-05-06') AS Days_since_start
,DATEDIFF(WEEK, '2023-01-01', '2024-05-06') AS Week_since_start
,DATEDIFF(MONTH, '2023-01-01', '2024-05-06') AS Month_since_start
,DATEDIFF(QUARTER, '2023-01-01', '2024-05-06') AS Quarter_since_start
,DATEDIFF(YEAR, '2023-01-01', '2024-05-06') AS Year_since_start
6.SQL datetime format using Format() function:
The FORMAT function in SQL Server is used to format date and time values into a specific string representation. It allows you to present date and time data in a customized format for display purposes. The format is defined using a format string that consists of placeholders for various date and time components.
Here are some examples of using the FORMAT function:
a. Formatting Date in SQL:
we want to fetch results sql datetime format dd mm yyyy. Let's see how we can achieve it
SELECT FORMAT('2023-08-17', 'dd/MM/yyyy') AS Formatted_Date;
b. Formatting Time in SQL:
SELECT FORMAT('15:30:45', 'hh:mm:ss tt') AS formatted_time;
O/P:
c. Formatting DateTime in SQL:
we want to fetch results sql datetime format dd mm yyyy hh mm ss. Let's see how we can achieve it.
d. Custom Formatting:
Here we are going to learn that how we can achieve custom datetime formatting in SQL.
SELECT FORMAT('2023-08-17', 'dddd, d MMMM yyyy') AS custom_formatted_date;
O/P:
Keep in mind that the FORMAT function is available in SQL Server 2012 and later versions. While it provides flexibility in formatting date and time values, it might impact performance for large datasets due to its conversion process. Always test and evaluate its usage in your specific scenarios.
SQL datetime format codes:
Here’s list of arguments that can be used with FORMAT() function.
d: Day of the month as digits (1-31).
dd: Day of the month as digits with leading zero (01-31).
ddd: Abbreviated day name (Sun, Mon, Tue, etc.).
dddd: Full day name (Sunday, Monday, Tuesday, etc.).
M: Month as digits (1-12).
MM: Month as digits with leading zero (01-12).
MMM: Abbreviated month name (Jan, Feb, Mar, etc.).
MMMM: Full month name (January, February, March, etc.).
yy: Last two digits of the year (00-99).
yyyy: Full year (0001-9999).
h: Hour in 12-hour clock (1-12).
hh: Hour in 12-hour clock with leading zero (01-12).
H: Hour in 24-hour clock (0-23).
HH: Hour in 24-hour clock with leading zero (00-23).
m: Minutes (0-59).
mm: Minutes with leading zero (00-59).
s: Seconds (0-59).
ss: Seconds with leading zero (00-59).
t: First character of AM/PM designator (A/P).
tt: AM/PM designator (AM/PM).
Conclusion:
SQL Server's date and time functions are the compass guiding us through the temporal landscape of data. By seamlessly extracting, manipulating, and presenting time-related insights, SQL empowers us to unravel hidden patterns, make informed decisions, and enhance our understanding of the data's context. As we traverse the realm of cricket match statistics, SQL Server proves to be an indispensable ally in our journey to decipher the stories that unfold over time.
.jpg)
.jpg&description=Time matters: Exploiting SQL DateTime Functions){kind=link}
0 Comments