Date Time functions

Date and Time Functions are the part of SQL server System functions, They can be found under the programmability folder -->Functions--> System Functions -->Date and Time Functions

Date time in general represented as YYYY-MM-DD hh:mm:ss.nn

List function of Date Time functions

• Current_Timestamp : This is an ANSI function , that will get you he current date and time.It doesn't  take any arguments

Example:

Print CURRENT_TIMESTAMP

Jul 10 2017 12:30PM

       

• Dateadd()  : Dateadd function helps you to build a date and time, by adding interval value to the specified date part , interval number is signed number

               

syntax  DATEADD (datepart , number , date ) 

Example

print dateadd(dd,1,'07/10/2017')

print dateadd(dd,-1,'07/10/2017')

Jul 11 2017 12:00AM

Jul 9 2017 12:00AM 

• Datediff() : Datediff  functions helps you to find the difference between two given date times in the requested datepart format  

  syntax DATEDIFF ( datepart , startdate , enddate ) 

Example

print datediff(dd,'07/10/2017','07/11/2017')

print datediff(hh,'07/10/2017','07/11/2017')

1

24

• Datename(): Datename function helps you to print the name of the date part for the given datetime

syntax DATENAME ( datepart , date )

Example
print Datename (mm,'07/11/2017 23:00:03')

print Datename (yyyy,'07/11/2017 23:00:03')

print Datename (hh,'07/11/2017 23:00:03')

July

2017

23 

• Datepart() : Datename function helps you to print the number of the date part for the given datetime

 syntax DATEPART ( datepart , date ) 

Example
 print Datepart (mm,'07/11/2017 23:00:03')

print Datepart (yyyy,'07/11/2017 23:00:03')

print datepart (hh,'07/11/2017 23:00:03')

7

2017

23

• Day() : Day function returns you the no of day from the give date time input

syntax Day(Date)

Example
print Day ('07/10/2017')
10 

• Getdate() : Getdate function returns date and time off today

syntax Getdate()

Example

print getdate()

Jul 10 2017 3:38PM

•  Getutcdate() : Getutcdate function returns date time off today

syntax Getutcdate()

Example

select getdate() as Todays_date,Getutcdate() as Today_UTC

Todays_date                       Today_UTC

2017-07-10 15:47:50.500   2017-07-10 22:47:50.500

• Isdate(): Isdate function helps us to identify wheather the input values is date or not, if it is a date it would return 1

syntax Isdate(string)

Example

print isdate('07/10/2017')

print isdate('cat')

1

0

• Month() : Month function returns integer value of the month from the input date time provided

Syntax Month(date)

Example
print Month('07/10/2017')
7

• Sysdatetime(): Sysdatetime function returns date time of the time it was executed, it difference from getdate only to the level of granularity  

Syntax sysdatetime()

Example
print getdate()

print Sysdatetime()

Jul 10 2017 4:14PM

2017-07-10 16:14:24.8253614

• Sysdatetimeoffset(): Sysdatetimeoffset function returns date time of the time it was executed along with offset value

For example server in which we executing the command is in PST/PDT it would get -7 value where as in ESDT/EDT it would be -4


syntax Sysdatetimeoffset()

Example
print Sysdatetimeoffset()
2017-07-10 16:29:26.6988611 -07:00

• sysutcdatetime(): sysutcdatetime function returns sysdatetime in utc formate

syntax sysutcdatetime()

Example

print Sysutcdatetime()

2017-07-10 23:36:27.1041777
  

• Switchoffset() : Switchoffset function returns a datetimeoffset value that is changed from the stored time zone offset to a specified new time zone offset. 

syntax Switchoffset ( dattimeoffset, time_zone )

Example

print Switchoffset('07/10/2017 7:45:50.71345 -5:00', -7)

2017-07-10 12:38:50.7134500 -00:07

• Todatetimeoffset() : Function requires 2 input parameters first one is the date time with offset value second parameter is the new offset value which you would like the original to be changed too

syntax TODATETIMEOFFSET ( expression , time_zone )

Example
DECLARE @todaysDateTime datetime2;

SET @todaysDateTime = '2017-07-10 16:51:42.8066667 -04:00'

SELECT TODATETIMEOFFSET (@todaysDateTime, '-07:00');

2017-07-10 16:51:42.8066667 -07:00

• Year() : year function returns the year of the input date to the function

syntax Year(string)
Example

print year(getdate())
2017