Time SQL Function
The Time() functions takes a date or time, allows you to apply modifiers and then returns the UTC time.
As default value it will return UTC time. To work with Local Date Time you can use DateTimeFormat function e.g.
SELECT Date( DateTimeFormat(GetDate(), 'yyyy-MM-dd HH:mm:ss', 'en-US') ); or just the date SELECT Date( DateTimeFormat(GetDate(), 'yyyy-MM-dd', 'en-US') ); and like adding one day
SELECT Date( DateTimeFormat(GetDate(), 'yyyy-MM-dd HH:mm:ss', 'en-US') , '+1 day') ; these tricks will let you use local datetime.
Syntax for Time() function is Time (timestring , [ modifier1, modifier2, ... modifier_n ] )
A timestring value and modifier1, modifier2, ... modifier_n are optional parameters.
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 Time as Hour:Minutes:Seconds */
SELECT Time('Now');
SELECT Time();
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.