Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.
Loading

1st Prize - Nokia Lumia 710


Win 31 copies of

SQLServer Quiz 2012 - Using datetime functions inside SQL Server have always been a struggle and there are multiple option

  • Using datetime functions inside SQL Server have always been a struggle and there are multiple options. There have been interesting usages of GETDATE but interesting functions like SYSDATETIME(), GETUTCDATE() and SYSYTCDATETIME() functions were also introduced. When would you use these functions and what are their differences?

    Posted on 01-27-2012 00:00 |
    InterviewQuestions
    73 · 2% · 775

4  Answers  

Subscribe to Notifications
  • Score
    10

    GETDATE() - returns the current database system timestamp. This value is derived from the operating system of the computer on which the instance of SQL Server is running. If you are connected to the SQL server remotely then the timestamp displayed will be the timestamp of the SQL server machine and not your local machine.

    Usage: SELECT GETDATE() as [ServerTime]
    

    SYSDATETIME() - Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is not included. Return datatype is datetime2(7).It is a nondeterministic function.

    GETUTCDATE() - Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The date and time is returned as UTC time (Coordinated Universal Time).Return datatype is datetime.It is a nondeterministic function.To be clear,Returns the current UTC time. This value is derived from the operating system of the computer on which the instance of SQL Server is running. This can be used to store the timestamp that is independent of Time Zones.

    Usage: SELECT GETUTCDATE() as [UTCTime]
    

    SYSUTCDATETIME() - Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The date and time is returned as UTC time (Coordinated Universal Time). Return datatype is datetime2(7).It is a nondeterministic function.

    In SQL Server 2005 (and previous versions) one would use GETDATE() to get the current date and time. In SQL Server 2008 this is still possible, but one can also use SYSDATETIME(). So what is exactly the difference?

    The function GETDATE() returns the seconds part of the time in three fractions. The function SYSDATETIME() however returns the seconds part of the time in seven fractions. This is demonstrated in the following example:

    select 'The current date and time is: ' , GETDATE()
    
    select 'The current date and time is: ' , SYSDATETIME()
    
    The current date and time is:   2008-08-21 16:28:50.340
    The current date and time is:   2008-08-21 16:28:50.3406250
    

    At first this may seem insignificant, but it is an important difference when using the data type DATETIME2. The data type DATETIME2 stores dates and times in a higher precious than the (old) data type DATETIME and therefore the difference between GETDATE() and SYSDATETIME() is important when using DATETIME2:

    declare @dt table (bigdt datetime2)
    
    insert into @dt 
    values 
        ( GETDATE() ) ,
        ( SYSDATETIME() ) 
    
    select * from @dt
    
    2008-08-21 16:35:01.7130000
    2008-08-21 16:35:01.7156250
    

    Now what happens when you use SYSDATETIME() in combination with the (old and) less precious data types DATETIME and SMALLDATETIME? Fortunately no error is returned, but instead the additional fractions are ignored:

    declare @dtold table (smalldt smalldatetime, normaldt datetime)
    
    insert into @dtold 
    values 
        ( SYSDATETIME() , SYSDATETIME() ) 
    
    select * from @dtold
    
    2008-08-21 16:39:00 2008-08-21 16:38:51.810
    

    So there is really no reason to keep on using GETDATE(). And therefore the simple advice is: in SQL Server 2008 always use SYSDATETIME() when you want to retrieve the current date and/or time.

    And by the way, the same thing applies to GETUTCDATE() and SYSUTCDATETIME(). So the second advice is: in SQL Server 2008 use GETUTCDATE() and SYSUTCDATETIME() appropriately depends on the accuracy in fractions.

    Apart, if you want the formatted way, you may have to explicitly use convert function to show the month name in the results as below for sys function.

    Select cast(GETDATE() as Varchar(10)) --**Jan 27 201**
    Select  cast(GETUTCDATE() as Varchar(10)) --**Jan 27 201**
    Select  cast(SYSDATETIME() as Varchar(10)) --**2012-01-27**
    Select  cast(SYSUTCDATETIME() as Varchar(10))--**2012-01-27**
    
    Replied on Jan 27 2012 12:38AM  . 
    Latheesh NK
    51 · 4% · 1178
  • Score
    8

    SYSDATETIME

    Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. SYSDATETIME is a nondeterministic function. Views and expressions that reference this function in a column cannot be indexed.

    Syntax : SYSDATETIME ( )


    GETUTCDATE

    Returns the current database system timestamp as a datetime value. The database time zone offset is not included. This value represents the current UTC time (Coordinated Universal Time). This value is derived from the operating system of the computer on which the instance of SQL Server is running.

    Transact-SQL statements can refer to GETUTCDATE anywhere they can refer to a datetime expression.

    GETUTCDATE is a nondeterministic function. Views and expressions that reference this function in a column cannot be indexed.

    Syntax : GETUTCDATE()


    SYSUTCDATETIME

    Returns a datetime2 value that contains the date and time of the computer on which the instance of SQL Server is running. The date and time is returned as UTC time (Coordinated Universal Time). The fractional second precision specification has a range from 1 to 7 digits. The default precision is 7 digits.

    Transact-SQL statements can refer to SYSUTCDATETIME anywhere they can refer to a datetime2 expression.

    SYSUTCDATETIME is a nondeterministic function. Views and expressions that reference this function in a column cannot be indexed.

    Syntax: SYSUTCDATETIME ( )


    GETDATE

    Returns the current database system timestamp as a datetime value without the database time zone offset. This value is derived from the operating system of the computer on which the instance of SQL Server is running.

    Transact-SQL statements can refer to GETDATE anywhere they can refer to a datetime expression.

    GETDATE is a nondeterministic function. Views and expressions that reference this function in a column cannot be indexed.

    Syntax: GETDATE ( )


    Example ::

    SELECT SYSDATETIME() AS SYSDATETIME    
        ,SYSUTCDATETIME() AS SYSUTCDATETIME    
        ,GETDATE() AS GETDATE
        ,GETUTCDATE() AS GETUTCDATE;
    

    Here is the result set.

    SYSDATETIME() 2007-04-30 13:10:02.0474381

    SYSUTCDATETIME() 2007-04-30 20:10:02.0474381

    GETDATE() 2007-04-30 13:10:02.047

    GETUTCDATE() 2007-04-30 20:10:02.047


    Microsoft introduced four new date data types in SQL Server 2008: time, date, datetime2, and datetimeoffset. The date and time data types allow for storing just the needed data, whereas the datetime2 data type provides a larger range of possible date values and greater precision than the existing datetime type. And datetimeoffset allows for dates to be stored along with an offset.

    here is diffrence table

    High-Precision System Date and Time Functions

    Function

    Syntax

    Return value

    Return data type

    Determinism

    SYSDATETIME

    SYSDATETIME ()

    Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is not included.

    datetime2(7)

    Nondeterministic

    SYSUTCDATETIME

    SYSUTCDATETIME ( )

    Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The date and time is returned as UTC time (Coordinated Universal Time).

    datetime2(7)

    Nondeterministic

    Lower-Precision System Date and Time Functions

    Function

    Syntax

    Return value

    Return data type

    Determinism

    GETUTCDATE

    GETUTCDATE ( )

    Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The date and time is returned as UTC time (Coordinated Universal Time).

    datetime

    Nondeterministic

    GETDATE GETDATE () Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is not included.

    datetime

    Nondeterministic

    Thanks

    Yogesh

    Replied on Jan 27 2012 1:40AM  . 
    Yogesh Kamble
    142 · 1% · 349
  • Score
    8

    GETDATE(),SYSDATETIME(),GETUTCDATE() and SYSUTCDATETIME() functions use the windows API GetSystemTimeAsFileTime() to fetch the current data time. The precision for this API is fixed to ~100 Nano seconds.
    Date time Functions SYSDATETIME (), SYSUTCDATETIME() are high precition functions and the functions GETDATE(), GETUTCDATE() are low precision functions.

    GETDATE()
    Returns the current database system timestamp as a datetime value without the database time zone offset. This value is derived from the operating system of the computer on which the instance of SQL Server is running.

    SYSDATETIME()
    Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is not included.

    GETUTCDATE()
    Returns the current database system timestamp as a datetime value. The database time zone offset is not included. This value represents the current UTC time (Coordinated Universal Time). This value is derived from the operating system of the computer on which the instance of SQL Server is running.

    SYSUTCDATETIME()
    Returns a datetime2 value that contains the date and time of the computer on which the instance of SQL Server is running. The date and time is returned as UTC time (Coordinated Universal Time). The fractional second precision specification has a range from 1 to 7 digits. The default precision is 7 digits.

    http://msdn.microsoft.com/en-us/library/ms186724.aspx

    Example

    SELECT 'GETDATE' AS FunctionName, GETDATE() AS DateTimeFormat
    UNION ALL
    SELECT 'SYSDATETIME' , SYSDATETIME() 
    UNION ALL
    SELECT 'GETUTCDATE', GETUTCDATE()
    UNION ALL
    SELECT 'SYSUTCDATETIME', SYSUTCDATETIME()
    

    GETDATE( ) and SELECT SYSDATETIME( ) both return the current date and time in your time zone. However, GETDATE( ) shows fractional seconds expressed in milliseconds (.333 second), and SYSDATETIME( ) shows fractional seconds expressed in nanoseconds (.3333333 second). SYSDATETIME( ) return similar results but their precisions differ.

    What time is it right now in the UK? UTC is Coordinated Universal Time, formerly known as Greenwich Mean Time (GMT). (UTC is also known by the terms zulu time, world time, and universal time.)

    SELECT GETUTCDATE( ) will show the current time expressed in terms of UTC. GETUTCDATE( ) is less precise than SYSUTCDATETIME ( ). However there is a UTC function that gets down to the nanoseconds, SYSUTCDATETIME( ). When we run all 4 of these functions together. We see the two top times in my local time zone (in my case the Pacific time zone) and the two bottom times in UTC.

    The difference between GETDATE() and GETUTCDATE() is time zone number of the SQL Server machine. The example below gives you a good example:

    DECLARE @local_time DATETIME;
    DECLARE @gmt_time DATETIME;
    SET @local_time = GETDATE();
    SET @gmt_time = GETUTCDATE();
    SELECT 'Server local time: '
       + CONVERT(VARCHAR(40),@local_time);
    SELECT 'Server GMT time: '
       + CONVERT(VARCHAR(40),@gmt_time);
    SELECT 'Server time zone: '
       + CONVERT(VARCHAR(40),
          DATEDIFF(hour,@gmt_time,@local_time));
    GO
    --Output
    --Server local time: Jan 27 2012 10:59PM
    --Server GMT time: Jan 27 2012  5:29PM
    --Server time zone: 5
    

    Note that the local time is from SQL server machine, not your local machine if you are connected to the server remotely. The local time is also adjusted by day-light savings.

    GETDATE = Returns the day of the month for the specified date according to local time. GETUTCDATE() = Returns the day (date) of the month in the specified date according to universal time.

    “GETUTCDATE()” which is the current UTC time which does not vary by time of year or timezone. Therefore, all the time was based off the same timezone and it never changed for daylight savings. Using GETUTCDATE instead of GETDATE was a simple solution to a complicated problem to solve timezones and daylight savings changes.

    How to convert local DateTime to UTC DateTime?

    SELECT  DATEADD(hh, DATEDIFF(hh, GETDATE(), GETUTCDATE()), GETDATE()) AS 'UTC DateTime' ,
            GETDATE() AS 'Local DateTime'
    

    http://blog.sqlauthority.com/2011/08/16/sql-server-tips-from-the-sql-joes-2-pros-development-series-system-and-time-data-types-day-16-of-35

    http://riteshshah.wordpress.com/category/getutcdate/

    Replied on Jan 27 2012 4:10AM  . 
    Mitesh Modi
    18 · 10% · 3080
  • Score
    8

    1. GETDATE()

    Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is not included. It is a nondeterministic function.

    2. SYSDATETIME()

    Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is not included.

    3. GETUTCDATE()

    Returns a datetime2(7) value. Returns the current database system timestamp as a datetime value. The database time zone offset is not included. This value represents the current UTC time (Coordinated Universal Time). This value is derived from the operating system of the computer on which the instance of SQL Server is running.

    4. SYSUTCDATETIME()

    Returns a datetime2 value that contains the date and time of the computer on which the instance of SQL Server is running. The date and time is returned as UTC time (Coordinated Universal Time). The fractional second precision specification has a range from 1 to 7 digits. The default precision is 7 digits.

    Details

    SYSDATETIME and SYSUTCDATETIME have more fractional seconds precision than GETDATE and GETUTCDATE. SYSDATETIMEOFFSET includes the system time zone offset. SYSDATETIME, SYSUTCDATETIME, and SYSDATETIMEOFFSET can be assigned to a variable of any of the date and time types.

    Usages

    SYSDATETIME and SYSUTCDATETIME can be used where the time granularity is more important. for example, at what fraction of time the insertion took place. It matters when you sync data from one location to another to avoid duplications. If you are sync the data between two different time zones, SYSUTCDATETIME comes into play. But is much precission is not required, you use GETDATE and GETUTCDATE.

    SYSDATETIME and SYSUTCDATETIME were not in the list before SQL Server 2005.

    Replied on Feb 6 2012 6:29AM  . 
    ATif-ullah Sheikh
    132 · 1% · 391

Your Answer


Sign Up or Login to post an answer.
Please note that your answer will not be considered as part of the contest because the competition is over. You can still post an answer to share your knowledge with the community.

Copyright © Rivera Informatic Private Ltd.