DateTime Functions

• DateTime Function in SQL Server

• GETDATE()

• DATEPART()

• DATEDIFF()

• DATENAME()

• DAY()

• MONTH()

• YEAR()

GETDATE():

 This value is derived from the operating system of the computer on which the instance of SQL Server is running.

SELECT GETDATE() as TODAY

Ans:

DATEPART():

It returns the part of the date like YEAR,MONTH,WEEK,DAY,QUARTER,DAYOFYEAR,WEEKDAY,HOUR,MINUTE
,SECOND and MILLISECOND



 DATEDIFF():

Returns the count of the specified datepart boundaries crossed between the specified startdate and enddate.

ex:

SELECT DATEDIFF(YEAR,HIREDATE,GETDATE())AS DIFFERENCE FROM EMP

DATENAME():

SELECT DATENAME(DAY, '2007-06-01')as dayinmonth,DATENAME(DY, '2007-06-01')as dayinyear,DATENAME(WEEKDAY, '2007-06-01')as weekday;

ans:

DAY():

Returns an integer representing the day (day of the month) of the specified date.

Ex:

SELECT DAY(GETDATE())AS DAYOFMONTH

MONTH():

SELECT MONTH(GETDATE())AS MONTHOFYEAR

YEAR():

SELECT YEAR(GETDATE())AS YEAR

CONVERSIONS

note:conversions are collected from different websites
declare @date datetime

set @date='2012-03-16 '

SELECT convert(varchar, @date, 103)
 

for further reference below you can find all the possible format for a date

100 – mon dd yyyy hh:mmAM (or PM)
    – Oct  2 2008 11:01AM         
101 – mm/dd/yyyy - 10/02/2008                 
102 – yyyy.mm.dd – 2008.10.02          
103 – dd/mm/yyyy
104 – dd.mm.yyyy
105 – dd-mm-yyyy
106 – dd mon yyyy
107 – mon dd, yyyy
108 – hh:mm:ss
109 – mon dd yyyy hh:mm:ss:mmmAM (or PM)
    – Oct  2 2008 11:02:44:013AM  
110 – mm-dd-yyyy
111 – yyyy/mm/dd
112 – yyyymmdd
113 – dd mon yyyy hh:mm:ss:mmm
    – 02 Oct 2008 11:02:07:577    
114 – hh:mm:ss:mmm(24h)
120 – yyyy-mm-dd hh:mm:ss(24h)
121 – yyyy-mm-dd hh:mm:ss.mmm
126 – yyyy-mm-ddThh:mm:ss.mmm
     – 2008-10-02T10:52:47.513

 Remaining date functions in sql2012

SELECT Datefromparts(2010, 5, 14) AS [DateParts]

SELECT Dateadd(mm, 2, Getdate()) AS [DateAdd]

SELECT Datediff(mm, '2014-01-01', Getdate()) AS [DateDiff]

SELECT Datename(day, Getdate()) AS [dayname]

SELECT Datepart(mm, Getdate()) AS [DATEPART]

SELECT Datetime2fromparts(2014, 12, 30, 4, 5, 6, 7, 1) AS [DATETIME2FROMPARTS]

SELECT Datetimefromparts(2010, 10, 25, 10, 11, 22, 9) AS[DATETIMEFROMPARTS]

SELECT Switchoffset(Sysdatetimeoffset(), '+06:30') AS [SWITCHOFFSET]

SELECT Datetimeoffsetfromparts(2014, 08, 25, 10, 10, 20, 1, 5, 30, 6)

AS [DATETIMEOFFSETFROMPARTS]

SELECT Sysdatetime() AS [SYSDATETIME]

SELECT Sysdatetimeoffset() AS [SYSDATETIMEOFFSET]

SELECT Getdate() AS [GETDATE]

SELECT Getutcdate() AS [GETUTCDATE]

SELECT Isdate('2014-06-20') AS [ISDATE]

SELECT Eventdata() AS [EVENTDATA]

SELECT Objidupdate(1) AS [OBJIDUPDATE]

SELECT Datalength('2014-05-25') AS [DATALENGTH]

SELECT Stats_date(2, 12563) AS [STATS_DATE]

SELECT Sysdatetime() AS [SYSDATETIME]

SELECT Sysdatetimeoffset() AS [SYSDATETIMEOFFSET]

SELECT Sysutcdatetime() AS [SYSUTCDATETIME]

SELECT Todatetimeoffset('2014-06-03', 500) AS [TODATETIMEOFFSET]