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.
4 comments:
update tblDateTime set c_datetimeoffset = 2014年05月12日 13:37:27.8430000 +10:00
Reply Deletewhere 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,
Reply Deleteyour 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.
It would be great if you can help share these free resources
[フレーム]