Thursday, August 15, 2013

SQL DATEPART


SQL DATEPART


SQL DATEPART function is used to extract or display specified date part from the existing date. This function always return integer value. For example, If you want to extract year, month or quarter from the existing Date, you can use this SQL Datepart function.

SQL DATEPART Syntax

Datepart: It is the part of a given date which we are going to display as output. Following table will display the list of available datepart argument in SQL Server 2014
DatepartAbbreviationsDescription
yearyy,yyyyThis will display the year value from the given date
quarterqq, qThis will find and display the quarter number from the given date
monthmm, mThis will find and display the month number from the given date
dayofyeardy, yThis will find and display the day of a year number from the given date (1 to 365)
daydd, dThis will find and display the day number from the given date (1 to 31)
weekwk, wwThis will display the Week number from the given date
weekdaydw, wThis will display the Week days number from the given date (0 as Sunday & 6 as Saturday)
hourhhThis will display the Hour value present in the given date
minutemi, nThis will display the Minute Value present in the given date
secondss, sThis will display the Seconds Value present in the given date
millisecondmsThis will display the Milliseconds Value present in the given date
microsecondmcsThis will display the microsecond Value present in the given date
nanosecondnsThis will display the Nanoseconds Value present in the given date
TZOffsettzThis will display the Time Zone Offset Value present in the given date
ISO_WEEKisowk, isowwThis will display the Iso Week Number from the given date
Date: Please specify the valid date as second argument. It can be column, expression or any variable.

SQL DATEPART Function Example 1

In this example we are going to declare a variable of datetime2 data type. Let us assign valid date to that variable and perform all the available datepart operation
SQL CODE
OUTPUT
SQL DATEPART 1
ANALYSIS
We declared one variable and assigned the date and time to that variable
We asked the SQL Datepart function to display the year value from the given date
We asked the SQL Datepart function to display the Month number from the given date
and so on

SQL DATEPART Function Example 2

In this example, we are going to use one of the custom SQL table to perform SQL Datepart operations on Hire Date column.
SQL CODE
OUTPUT
SQL DATEPART 2
ANALYSIS
Below statement will display the Year value from the Hire Date Column
Below statement will display the Quarter value from the Hire Date Column
Thank You for Visiting Our Blog