SQL SERVER 2008 – FUNCTIONS In SQL SERVER 2008 – Date and Time Functions

The following functions perform an operation on a date and time input value and return a string, numeric, or date and time value.

GETDATE(): Returns the current date and time of the server in SQL Server standard internal format.

SELECT GETDATE()

DAY(date): Returns an integer representing the DAY of the specified date, which has to be specified in standard SQL Server date format ‘mm/dd/yy’.

SELECT DAY(GETDATE())

SELECT DAY(‘10/24/78’)                                        OUTPUT: 24

 

MONTH(date): Returns an integer representing the MONTH of the specified date, which has to be specified in standard SQL Server date format ‘mm/dd/yy’.

SELECT MONTH(GETDATE())

SELECT MONTH(‘10/24/78’)                                   OUTPUT: 10

 

YEAR(date): Returns an integer representing the YEAR of the specified date, which has to be specified in standard SQL Server date format ‘mm/dd/yy’.

SELECT YEAR(GETDATE())

SELECT YEAR(‘10/24/78’)                                      OUTPUT: 1978

 

DATENAME(datepart, date): Returns a character string representing the specified datepart of the specified date, datepart is the parameter that specifies the part of the date to return. The following table lists dateparts and abbreviations recognized by Sql Server:

 

Datepart Abbreviations
year yy, yyyy
quarter qq, q
month mm, m
dayofyear dy, y
day dd, d
week wk, ww
weekday dw
hour hh
minute mi, n
second ss, s
millisecond ms

 

SELECT DATENAME(mm, ‘10/24/78’)                   OUTPUT: October

SELECT DATENAME(dd, ‘10/24/78’)                    OUTPUT: 10

 

DATEPART(datepart, date): This is same as DATENAME function but the only difference is weekday (dw) of DATEPART function returns a number that corresponds to the day of the week, for example: Sunday = 1, Saturday = 7, where as in the case of DATENAME returns the value in string format that is Sunday, Monday, … Saturday.

 

DATEADD(datepart, number, date): Returns a new datetime value based on adding an interval to the specified date, datepart is the value that has to be added and number is the interval.

SELECT DATEADD(dd, 30, GETDATE()) –Adds 30 days to GETDATE().

SELECT DATEADD(mm, 16, GETDATE()) –Adds 16 months to GETDATE().

 

DATEDIFF(datepart, startdate, enddate): Returns the difference between the start and end dates in the give datepart format.

SELECT DATEDIFF(yy, ‘10/24/78’, GETDATE())

 

GETUTCDATE()-Returns the datetime value representing the current UTC time (Coordinated Universal Time or Greenwich Mean Time).

SELECT GETUTCDATE()

Reference : Dilip Kumar Jena ( https://sqlexplore.wordpress.com )

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s