DateTime SQL Function

The DateTime() functions takes a date and allows you to apply modifiers and then returns the date as UTC date or time. As default value these functions return UTC time, which can be converted to local time in 24 hour format e.g. SELECT DateTime('Now', 'localtime'); For .Net style date time and more control see getdate() , GetUTCDate() , DateAdd , DateDiff() and DateTimeFormat() functions. To work with Local Date Time you can use DateTimeFormat function e.g. SELECT DateTime( DateTimeFormat(GetDate(), 'yyyy-MM-dd HH:mm:ss', 'en-US') ); or just the date SELECT DateTime( DateTimeFormat(GetDate(), 'yyyy-MM-dd', 'en-US') ); and like adding one day SELECT DateTime( DateTimeFormat(GetDate(), 'yyyy-MM-dd HH:mm:ss', 'en-US') , '+1 day') ; these tricks will let you use local datetime.

Syntax is DateTime (Date or timestring , [ modifier1, modifier2, ... modifier_n ] )
A Date or timestring value and modifier1, modifier2, ... modifier_n are optional. Modifiers that are applied to the date time string are applied in order and are cumulative.

Date Time string Explanation
now now is a literal used to return the current date
YYYY-MM-DD Date value formatted as 'YYYY-MM-DD'
YYYY-MM-DD HH:MM Date value formatted as 'YYYY-MM-DD HH:MM'
YYYY-MM-DD HH:MM:SS Date value formatted as 'YYYY-MM-DD HH:MM:SS'
YYYY-MM-DD HH:MM:SS.SSS Date value formatted as 'YYYY-MM-DD HH:MM:SS.SSS'
HH:MM Date value formatted as 'HH:MM'
HH:MM:SS Date value formatted as 'HH:MM:SS'
HH:MM:SS.SSS Date value formatted as 'HH:MM:SS.SSS'
YYYY-MM-DDTHH:MM Date value formatted as 'YYYY-MM-DDTHH:MM' where T is a literal character separating the date and time portions
YYYY-MM-DDTHH:MM:SS Date value formatted as 'YYYY-MM-DDTHH:MM:SS' where T is a literal character separating the date and time portions
YYYY-MM-DDTHH:MM:SS.SSS Date value formatted as 'YYYY-MM-DDTHH:MM:SS.SSS' where T is a literal character separating the date and time portions
DDDDDDDDDD Julian date number


modifier1, modifier2, ... modifier_n can be one or more of the following:

Modifier Explanation
[+-]NNN years Number of years added/subtracted to the date
[+-]NNN months Number of months added/subtracted to the date
[+-]NNN days Number of days added/subtracted to the date
[+-]NNN hours Number of hours added/subtracted to the date
[+-]NNN minutes Number of minutes added/subtracted to the date
[+-]NNN seconds Number of seconds added/subtracted to the date
[+-]NNN.NNNN seconds Number of seconds (and fractional seconds) added/subtracted to the date
start of year Shifting the date back to the start of the year
start of month Shifting the date back to the start of the month
start of day Shifting the date back to the start of the day
weekday N Moves the date forward to the next date where weekday number is N
(0=Sunday, 1=Monday, 2=Tuesday, 3=Wednesday, 4=Thursday, 5=Friday, 6=Saturday)
unixepoch Used with the DDDDDDDDDD timestring to interpret the date as UNIX Time (ie: number of seconds since 1970-01-01)
localtime Adjusts date to localtime, assuming the timestring was expressed in UTC
utc Adjusts date to utc, assuming the timestring was expressed in localtime


		/* Following will output UTC Date Year-Month-Day or YYYY-MM-DD */ 
SELECT DateTime('Now');
SELECT DateTime();

/* Following will output UTC Time as Hour:Minutes:Seconds */
SELECT DateTime('Now');
SELECT Time();

/* Following will output Complete UTC Date and Time */
SELECT DateTime('Now');
SELECT DateTime();

/* Following will add a day in UTC date time */
SELECT DateTime('Now', '+1 Day');

Date Time functions are extensively used in database and many columns store these values, these functions provide easy manner to add subtract date or time value in UTC time zone.