Date and Time Functions
In this post we will discuss date and time functions in SQL server 2016. SQL support many date and time functions that are used to manipulate date and time.
- GETDATE() function returns the current date and time in the SQL server instance you are connected in the DATETIME data type.
- GETDATE() function is not SQL standard.
select GETDATE() as todaydate;
- Returns a DATETIME2 value that contains the date and time of the computer on which the SQL server is running.
select SYSDATETIME() as todaydate;
- Returns a DATETIME value of the computer on which SQL server is running.
- The date and time is returned as UTC(Coordinated Universal Time).
select SYSUTCDATETIME() as utcdate;
- Returns an integer that represent the specified datepart of the specific date.
- Using DATEPART() function we can extract date or year or month from any given date.
- Date part must be one of the below.
|Day of year|
select DATEPART(ss,GETDATE()) as exsecond;
- Returns a date value for the specified year, month, day.
DATEFROMPARTS(year, month, day)
select DATEFROMPARTS(2017,11,06) as datecreated;
- Returns a time value for the specified time and with the specified precision.
TIMEFROMPARTS(hour, minute, second, fraction, precision)
select TIMEFROMPARTS(06,56,55,2,7) as timecreated;
- Returns a datetimeoffset(7) value that contains the date and time of the computer on which SQL is running.
- The time zone offset is included.
select SYSDATETIMEOFFSET() as dateoffset;
2017-11-06 18:59:40.4292903 +05:30