SQL Server Journey with SQL Authority

Posted in Pinal Dave, SQL, SQL Authority, SQL DateTime, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, SQLServer, T SQL, Technology | 46 Comments

46 Responses

1. on June 10, 2010 at 7:15 am | Reply Triadsql

   I didn’t know that and I’m kind of glad I haven’t run into a situation where it has mattered. This is good info to keep in mind in case I need this. How did you ever come across this?

   
   
2. on June 10, 2010 at 7:21 am | Reply @DrivingMSSQL

   Here is a guess, GETDATE is time off the DC oppose to the server time??? Your intro sentence is dead on.

   I have enjoyed your blog for several years, thanks for your dedication!

   
   
3. on June 10, 2010 at 8:06 am | Reply kaushal

   I was unaware of the second function SYSDATETIME(). Thanks for posting. and again same question: I wonder how did you come across this?

   
   
4. on June 10, 2010 at 8:30 am | Reply Jacob Sebastian

   Until SQL Server 2005, there was only one function, GETDATE() which returns a DATETIME value. A DATETIME value has precision upto 3 milliseconds.

   SQL Server 2008 introduced a new data type DATETIME2 which gives precision upto 100 nanoseconds. But if you use the GETDATE() function and assign the result to a DATETIME2 value, you would loose the precision (nanoseconds).

   So a new function was introduced which returns a DATETIME2 data type – having precision upto 100 nanoseconds.

   The major difference is the return value TYPE of each function. One returns DATETIME and the other DATETIME2.

   
   
5. on June 10, 2010 at 8:58 am | Reply Yastas

   I did not know the difference between that sentences. Thanks for the info.

   Regards.

   
   
6. on June 10, 2010 at 10:24 am | Reply Marko Parkkola

   Well, I knew that there is some differencies between functions and datetime data types as Jacob described above, but I wasn’t aware of this particular one.

   I’ve took a habit of consulting MSDN every time I have doubts about something. For example I check built-in function parameters every time from MSDN. That is an easy way to find out things like this.

   
   
7. on June 10, 2010 at 10:47 am | Reply Tahir

   great Pinal!

   I have never come across it. Thanks a lot for your contribution. I have learnt a lot from this blog.

   Another question.

   Pinal I want to appear in 70-432 exam,
   I have ready two, three relevant books, one books designed by Syngress another by Microsoft for this certification.

   Can you suggest a book which can help me pass the exam.

   What about brain dumps for this exam.

   Thank You.

   Tahir

   
   
8. on June 10, 2010 at 4:13 pm | Reply Biju.K.S

   Hi,
   i have searched about it and concluded Mr. Jacob Sebastian is correct.
   But please any one mention the practical use of it

   thanks,
   biju

   
   
9. on June 10, 2010 at 7:51 pm | Reply Harsha

   That is a good find pinal. I had Never come across this function. It would be good if someone points out the good use of this function. Most of time we use GetDate() function, I guess this function(SYSDATETIME) might be usefull if you have any time sensitive data…

   Thanks
   Harsha

   
   
10. on June 10, 2010 at 8:02 pm | Reply Madhivanan

    As per BOL, the return type of SYSDATETIME is datetime2(7)
    Also the query only works from version 2008 onwards only

    
    
11. on June 10, 2010 at 8:41 pm | Reply Tim C

    Wow, did not know this. I wonder if there is ever a need or use for such precision in a stored procedure?

    
    
12. on June 10, 2010 at 11:01 pm | Reply Kamal kant

    RESPECTED SIR,
    I WANT TO BE A MEMBER OF YOUR COMMUNITY. PLEASE ALLOW ME TO JOIN AS IT WILL HELP IN MY STUDIES AS I AM A STUDENT OF MCA.
    THANKS & REGARDS
    KAMAL KANT.

    
    
13. on June 11, 2010 at 5:28 am | Reply IgorA

    I didn’t know either, just never used the second one, but any way, you can’t store in the database milliseconds with precision greater than .000 and with step less than .003

    
    
14. on June 11, 2010 at 7:04 am | Reply SQL SERVER – Difference Between DATETIME and DATETIME2 Journey to SQL Authority with Pinal Dave

    [...] 11, 2010 by pinaldave Yesterday I have written a very quick blog post on SQL SERVER – Difference Between GETDATE and SYSDATETIME and I got tremendous response for the same. I suggest you read that blog post before continuing [...]

    
    
15. on June 11, 2010 at 12:27 pm | Reply Sriram

    hi, just never noticed it, but after trying it out where really it would be helpful, most of them that i use make use of the datetime data type and i can use getdate() with it and not sure where i can make use of the sysdatetime….

    
    
16. on June 12, 2010 at 1:46 am | Reply Ramdas

    Really not used SYSDATETIME since the need for such precision is not there for my line of business. It is interesting to note that such precision is available.

    
    
17. on June 12, 2010 at 7:01 am | Reply SQL SERVER – Difference Between DATETIME and DATETIME2 – WITH GETDATE Journey to SQL Authority with Pinal Dave

    [...] 12, 2010 by pinaldave Earlier I wrote blog post SQL SERVER – Difference Between GETDATE and SYSDATETIME which inspired me to write SQL SERVER – Difference Between DATETIME and DATETIME2. Now [...]

    
    
18. on June 14, 2010 at 10:48 am | Reply Nitin Sharma

    Thanks Pinal,
    No i didn’t know this.
    Thanks for Sharing.

    Regards,
    Nitin Sharma

    
    
19. on June 15, 2010 at 1:18 pm | Reply Børre Dalhaug

    Hi.
    I knew :-)
    I use sysdatetime when inserting rows in a log table. I need sysdatetime’s precision in that case.

    Regards
    Børre Dalhaug

    
    
20. on June 29, 2010 at 10:18 am | Reply Babu

    Hi Pinal,
    Thanks. Even i dont know the SYSDATETIME function in SQL.
    Can you tell us more about the unknown SQL functions.

    
    
21. on June 30, 2010 at 7:01 am | Reply SQLAuthority News – A Monthly Round Up of SQLAuthority Blog Posts Journey to SQL Authority with Pinal Dave

    [...] SQL SERVER – Difference Between GETDATE and SYSDATETIME [...]

    
    
22. on July 1, 2010 at 1:21 pm | Reply Sumit Thapar

    Hi pinal,
    Thanks for the article…truly speaking even i never knew about this.

    
    
23. on September 4, 2010 at 12:33 am | Reply Kuldip Dilip Deshmukh

    Hi Pinal,
    Thanks. Even i dont know the SYSDATETIME function in SQL.
    Can you tell us more about the unknown SQL functions.

    
    
24. on November 15, 2010 at 7:01 am | Reply SQL SERVER – Information Related to DATETIME and DATETIME2 Journey to SQL Authority with Pinal Dave

    [...] SQL SERVER – Difference Between GETDATE and SYSDATETIME [...]

    
    
25. on January 5, 2011 at 9:23 pm | Reply Tim

    The company I work for has updated to SS2008 but still wants to use SSMS 2005. Access 2003 and Excel 2003 is involved also. Datetime2 is the main issue

    Acces and Excel convert the dates to text. and using native 2005 T-sql I have to convert the date to text to extract it. There are hundreds of reports that are affected. Fortunately 2008 this is still running in test.

    Woud upgraning to Access and Excel 2010 and SSMS 2008 allow reading of the datetime2 field as a date?

    I don’t want to get into a power struggle and try to force them to change all the datetime2 fields to datetime. We don’t nor do we intend to use anything less than 1/100 of a second.

    
    
26. on January 18, 2011 at 2:15 pm | Reply New

    Nice. New to the SSMS 2008. Thanks

    
    
27. on July 19, 2011 at 7:00 am | Reply SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 19 of 31 Journey to SQLAuthority

    [...] In case of GETDATE, the precision is till milliseconds, and in case of SYSDATETIME, the precision is till nanoseconds.(Read More Here) [...]

    
    
28. on August 16, 2011 at 11:46 am | Reply Varinder Sandhu

    No i didn’t know this, thanks for sharing …

    Please refer this

    http://wiki.evident.nl/Default.aspx?Page=SYSDATETIME%20versus%20GETDATE&AspxAutoDetectCookieSupport=1

    
    
29. on September 5, 2011 at 6:53 pm | Reply Arunkarthik Viswanath

    No dint know.. :( TFS..

    
    
30. on September 21, 2011 at 11:40 pm | Reply Srinivas Kadiyala

    NO..I DONT KNOW…
    THANKS FOR SHARING SIR…
    -SKC

    
    
31. on January 13, 2012 at 12:12 pm | Reply Mandar Pandit

    Thanks for Sharing Sir….Gr8….!

    
    
32. on January 28, 2012 at 7:02 am | Reply SQL SERVER – Lots of Date Functions – Find Right One to Use – Quiz – Puzzle – 27 of 31 « SQL Server Journey with SQL Authority

    [...] Questions and Answers ISBN: 1466405643 Page#133 Difference Between DATETIME and DATETIME2 Difference Between GETDATE and SYSDATETIME Get Time in Hour:Minute Format from a Datetime – Get Date Part Only from Datetime Datetime [...]

    
    
33. on February 23, 2012 at 11:15 am | Reply Molly

    Thank for your post
    I’m searching the right function for setting datetime2 and your post give me the answere.

    
    
34. on April 2, 2012 at 8:31 pm | Reply Priyank sheth

    You can use these functions when you generate time related reports like employee working hour report or for logs….

    
    
35. on June 10, 2012 at 11:08 am | Reply Brij Sharma

    I just ran into a problem where I got this exception
    “The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value”
    So I looked at you description of datetime2 and datetime and came here to see the real diffrence.

    Thanks.

    
    
36. on June 29, 2012 at 10:31 am | Reply kirti panchal

    I really don’t know this. thanks a lot and also this sites help me a lot when i am in difficult situation.

    Thanks

    
    
37. on August 8, 2012 at 12:41 am | Reply Manesh Joseph

    I was also not aware of this. While trying this function, I came across another function called SYSDATETIMEOFFSET in which the your time zone offset is also included.

    select SYSDATETIMEOFFSET()

    Result : 2012-08-07 15:06:55.4932851 -04:00

    
    
38. on September 6, 2012 at 4:09 pm | Reply Lokesh

    Thanks Pinal. Really its good.

    
    
39. on November 27, 2012 at 7:10 am | Reply kipb7

    Didn’t know… Thanks! Any idea why the time from getdate() and sysdatetime() are so different? SQL 2008 they often differ by .010 seconds, see this example:
    PRINT DATEDIFF(ms,GETDATE(),SYSDATETIME())
    WAITFOR DELAY ’00:00:00.01′
    go 50

    
    
40. on December 15, 2012 at 7:01 am | Reply SQL SERVER – Weekly Series – Memory Lane – #007 « SQL Server Journey with SQL Authority

    [...] Difference Between GETDATE and SYSDATETIME [...]

    
    
41. on January 9, 2013 at 10:13 am | Reply Maxkub99

    Thanks

    
    
42. on January 21, 2013 at 3:02 pm | Reply deepa

    Wonderful article..

    
    
43. on January 28, 2013 at 12:17 pm | Reply Veerabhadrarao Samudrala

    Hi , i want date as
    jan twenty eighth 2001 in ms sql.

    
    
    • on February 15, 2013 at 6:35 pm | Reply madhivanan

      If you want to show formatted dates in front end application, do the formation there

      
      
44. on February 4, 2013 at 10:00 pm | Reply Sagar

    Thanks Pinal…
    Your blog is very useful…..

    
    
45. on March 1, 2013 at 11:23 am | Reply rahul sahay (@rahulsahay123)

    Thanks pinal…..was totally unaware of sysdatetime…..

    
    

Leave a Reply Cancel reply

Enter your comment here...

Fill in your details below or click an icon to log in:

Email (required) (Address never made public)

Name (required)

Website

You are commenting using your WordPress.com account. ( Log Out / Change )

You are commenting using your Twitter account. ( Log Out / Change )

You are commenting using your Facebook account. ( Log Out / Change )

Cancel

Connecting to %s

Notify me of follow-up comments via email.

Notify me of new posts via email.