DateTime functions in SQL Server - Part 25

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.
FunctionDate Time FormatDescription
GETDATE()2012-08-31 20:15:04.543Commonly used function
CURRENT_TIMESTAMP2012-08-31 20:15:04.543ANSI SQL equivalent to GETDATE
SYSDATETIME()2012-08-31 20:15:04.5380028More fractional seconds precision
SYSDATETIMEOFFSET()2012-08-31 20:15:04.5380028 + 01:00More fractional seconds precision + Time zone offset
GETUTCDATE()2012-08-31 19:15:04.543UTC Date and Time
SYSUTCDATETIME()2012-08-31 19:15:04.5380028UTC 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:

  1. update tblDateTime set c_datetimeoffset = 2014-05-12 13:37:27.8430000 +10:00
    where 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'.

    ReplyDelete
    Replies
    1. Hi Nisar,

      This 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'

      Delete
  2. Hi Venkat,
    your 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.

    ReplyDelete

It would be great if you can help share these free resources