Convert smalldatetime to int

  • How can I go about converting smalldatetime into int(4)?

  • First you need to define the rule for your conversion.

    07/17/2007 5:16:00 PM must become - what?

    07/17/2007 8:20:00 PM must become - what?

    _____________
    Code for TallyGenerator

  • SELECT CAST(CONVERT(CHAR(8),somedate,112) AS INT)

    ...but it's an insane thing to do for so many reasons...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • ... and, that's just the date... doesn't include time...

    But, Serqiy is correct... there's a date format that's based on the number of seconds since the 01/01/1970.  There are other "numeric" date formats such as Microsofts DateSerial number... we need to know what the format is that you want.  Answering Serqiy's questions would be a great start

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • If the integer you want to convert to is UNIX time, this will do it:

    select datediff(ss,'19700101',MySmallDateTime)

    The functions in this script can be used to convert to/from SQL Server date time to UNIX Time.

    UNIX Time Conversion Functions:

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=66858

     

     

  • And if it suppose to be Excel days then ...

    And if it suppose to be ...

    So, question remains: what it suppose to be?

    _____________
    Code for TallyGenerator

  • After getting a little more info myself, it's supposed to be Unix time. The number of seconds since midnight UTC of January 1, 1970. Michael's solution works perfectly. Thanks.

  • Good guess on my part, huh?

    My experience is that posts like this are almost always looking for a conversion to/from UNIX time, so I took a shot at it.

     

     

  • Good guess? That was pure magic guess!!!! ...


    * Noel

  • I've mistakenly created a column as smalldatetime instead of smallint and have imported data into that table. The values in that column are all 1900-01-01 00:00:00.000 or 1900-01-04 00:00:00 or 1900-01-03 00:00:00.

    I have not found a successful way to convert smalldatetime to smallint, in such a way that it recovers original values.

    For example 1900-01-01 00:00:00.000 should really just be 1 and 1900-01-03 00:00:00 should just be 3.

    At this point should I just create a new column with smallint datatype, use case statement to populate it, delete smalldatetime column, rename new column to original name?

    --Quote me

  • DATEDIFF(dd, 0, [smalldatetime column]) + 1

    _____________
    Code for TallyGenerator

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply