In this video session we will learn about
1. DateTime data types
2. DateTime functions available to select the current system date and time
3. Understanding concepts - UTC time and Time Zone offset
There are several built-in DateTime functions available in SQL Server. All the following functions can be used to get the current system date and time, where you have sql server installed.
Note: UTC stands for Coordinated Universal Time, based on which, the world regulates clocks and time. There are slight differences between GMT and UTC, but for most common purposes, UTC is synonymous with GMT.
To practically understand how the different date time datatypes available in SQL Server, store data, create the sample table tblDateTime.
CREATE TABLE [tblDateTime]
(
[c_time] [time](7) NULL,
[c_date] [date] NULL,
[c_smalldatetime] [smalldatetime] NULL,
[c_datetime] [datetime] NULL,
[c_datetime2] [datetime2](7) NULL,
[c_datetimeoffset] [datetimeoffset](7) NULL
)
To Insert some sample data, execute the following query.
INSERT INTO tblDateTime VALUES (GETDATE(),GETDATE(),GETDATE(),GETDATE(),GETDATE(),GETDATE())
Now, issue a select statement, and you should see, the different types of datetime datatypes, storing the current datetime, in different formats.
1. DateTime data types
2. DateTime functions available to select the current system date and time
3. Understanding concepts - UTC time and Time Zone offset
There are several built-in DateTime functions available in SQL Server. All the following functions can be used to get the current system date and time, where you have sql server installed.
| Function | Date Time Format | Description |
| GETDATE() | 2012-08-31 20:15:04.543 | Commonly used function |
| CURRENT_TIMESTAMP | 2012-08-31 20:15:04.543 | ANSI SQL equivalent to GETDATE |
| SYSDATETIME() | 2012-08-31 20:15:04.5380028 | More fractional seconds precision |
| SYSDATETIMEOFFSET() | 2012-08-31 20:15:04.5380028 + 01:00 | More fractional seconds precision + Time zone offset |
| GETUTCDATE() | 2012-08-31 19:15:04.543 | UTC Date and Time |
| SYSUTCDATETIME() | 2012-08-31 19:15:04.5380028 | UTC Date and Time, with More fractional seconds precision |
Note: UTC stands for Coordinated Universal Time, based on which, the world regulates clocks and time. There are slight differences between GMT and UTC, but for most common purposes, UTC is synonymous with GMT.
To practically understand how the different date time datatypes available in SQL Server, store data, create the sample table tblDateTime.
CREATE TABLE [tblDateTime]
(
[c_time] [time](7) NULL,
[c_date] [date] NULL,
[c_smalldatetime] [smalldatetime] NULL,
[c_datetime] [datetime] NULL,
[c_datetime2] [datetime2](7) NULL,
[c_datetimeoffset] [datetimeoffset](7) NULL
)
To Insert some sample data, execute the following query.
INSERT INTO tblDateTime VALUES (GETDATE(),GETDATE(),GETDATE(),GETDATE(),GETDATE(),GETDATE())
Now, issue a select statement, and you should see, the different types of datetime datatypes, storing the current datetime, in different formats.
update tblDateTime set c_datetimeoffset = 2014-05-12 13:37:27.8430000 +10:00
ReplyDeletewhere c_datetimeoffset=2014-05-12 13:37:27.8430000 +00:00
this query throw the following error to me.....
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '13'.
Hi Nisar,
DeleteThis was new to me too!! but if you put the DToffset value within '' then it will work. I tried without '' just as you did and it gives me an error. Hope it helps others who are viewing this page..
update tblDateTime set c_datetimeoffset = '2014-07-22 13:37:27.8430000 +10:00'
where c_datetimeoffset = '2014-07-21 14:09:38.2930000 +00:00'
how can I get india time
DeleteHi Venkat,
ReplyDeleteyour videos are amazing , I am using SSMS 2005 and I can't create a column with data_type of [time] or [Date]
the only available option is [Dateandtime]
I don't know, but is there's something wrong with the program or with my code.?
Thank you.