SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Addict
    Join Date
    Nov 2002
    Location
    Maryland
    Posts
    307
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL Server SMALLDATETIME to MySQL?

    Hello,
    How do I convert the following SQL Server SMALLDATETIME to Unix Timestamp or MySQL date?

    CAST(0x9C4902A8 AS SmallDateTime)

    I do not have SQL server installed on my computer. I was able to manually convert a SQL server dump to a MySQL dump and import it into MySQL, but the only thing I couldn't change was the date.

    Any help is greatly appreciated.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    well, i can tell you what that value means, but it will be up to you to write the appropriate code to covert what you have
    The smalldatetime data type stores dates and times of day with less precision than datetime. SQL Server stores smalldatetime values as two 2-byte integers. The first 2 bytes store the number of days after January 1, 1900. The other 2 bytes store the number of minutes since midnight. Dates range from January 1, 1900, through June 6, 2079, with accuracy to the minute.
    -- http://msdn.microsoft.com/en-us/libr...v=sql.80).aspx
    so your value 0x9C4902A8 consists of two integers, 9C49 and 02A8

    hex 9C49 is decimal 40009, and january 1 1900 plus 40009 days is 2009-07-17

    hex 02A8 is decimal 680, and midnight plus 680 minutes is 11:20

    you can construct the actual datetime value as follows:
    Code:
    select '1900-01-01' 
     + interval cast(0x9C49 as signed) day
     + interval cast(0x02A8 as signed) minute
     as result
    but i'm not sure how you should break up the incoming data
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast DmitryS's Avatar
    Join Date
    Feb 2011
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    select datediff(ss, '1/1/1970', your_date_here)

    e.g.

    select datediff(ss, '1/1/1970', cast('1/1/1989' as smalldatetime)

  4. #4
    SitePoint Addict
    Join Date
    Nov 2002
    Location
    Maryland
    Posts
    307
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937 and DmitryS,
    With your help I was able to solve the problem. Thanks!!


Bookmarks

Posting Permissions

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