CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2019
    Posts
    15

    SQL Server - date conversion

    Hi,

    Select CONVERT(datetime, 0xffff2e4600000000)

    This SQL request does not give the correct date when running on my SQL server's installation.
    It gives for instance : 1753-01-01 00:00:00.000

    How can I fix it ?

    Thank you for help

  2. #2
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,495

    Re: SQL Server - date conversion

    Quote it

  3. #3
    Join Date
    Dec 2019
    Posts
    15

    Re: SQL Server - date conversion

    Quote Originally Posted by Arjay View Post
    Quote it

    Do you mean something like this : Select CONVERT(datetime, '0xffff2e4600000000') ?

    If so, it does not work. It gives an error ==> Échec de la conversion de la date et/ou de l'heure à partir d'une chaîne de caractères.

  4. #4
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    20,100

    Re: SQL Server - date conversion

    Quote Originally Posted by James West View Post
    Do you mean something like this : Select CONVERT(datetime, '0xffff2e4600000000') ?

    If so, it does not work. It gives an error ==> Échec de la conversion de la date et/ou de l'heure à partir d'une chaîne de caractères.
    The error message 9translated to English) means "Failed to convert date and / or time from a string".
    Do you try to convert from hex data rather than a "string"?
    Victor Nijegorodov

  5. #5
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    20,100

    Re: SQL Server - date conversion

    Quote Originally Posted by James West View Post
    Hi,

    Select CONVERT(datetime, 0xffff2e4600000000)

    This SQL request does not give the correct date when running on my SQL server's installation.
    It gives for instance : 1753-01-01 00:00:00.000

    How can I fix it ?

    Thank you for help
    Again: what result do you expect and where comes this magic value 0xffff2e4600000000 from?
    Victor Nijegorodov

  6. #6
    Join Date
    Dec 2019
    Posts
    15

    Re: SQL Server - date conversion

    I am expecting to get a recent date like 2019-09-xx or something in 2020 not for two or three century less

  7. #7
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    20,100

    Re: SQL Server - date conversion

    Quote Originally Posted by James West View Post
    I am expecting to get a recent date like 2019-09-xx or something in 2020 not for two or three century less
    Then where does this magic value 0xffff2e4600000000 come from?
    Victor Nijegorodov

  8. #8
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    20,100

    Re: SQL Server - date conversion

    Quote Originally Posted by James West View Post
    I am expecting to get a recent date like 2019-09-xx or something in 2020 not for two or three century less
    In such a case the hex (or binary) you would convert from should be look like (according to my SSMS 2014)
    0x323031392D30392D33300000000000000000000000000000000000000000 (for the date '2020-09-30')
    Victor Nijegorodov

  9. #9
    Join Date
    Dec 2019
    Posts
    15

    Re: SQL Server - date conversion

    Very interesting !
    I will check in this way.
    Could you explain more ?

  10. #10
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    20,100

    Re: SQL Server - date conversion

    Quote Originally Posted by James West View Post
    Could you explain more ?
    What "more"?
    I just read the MSDN (docs.microsoft.com)
    Victor Nijegorodov

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured