Post reply

Convert INT to datetime

  • August 11, 2008 at 7:43 am

    #189188

    Hi all,

    I was wondering how i can convert the following int value to datetime (ie 20090101)

    I have the following SQL but it doesnt seem to work and returns back the same value

    SELECT CONVERT(VARCHAR,'20070101',111) as datetime

    any help will be much appreciated.

    thanks

  • August 11, 2008 at 8:01 am

    #856088

    SELECT CONVERT(CHAR(10), STR(Col1, 8, 0), 111)

    FROM Table1

    N 56°04'39.16"
    E 12°55'05.25"

  • August 11, 2008 at 8:06 am

    #856093

    Hi, ive just tried the code provided above and still the same result

    ie

    SELECT CONVERT(CHAR(10), STR('20070101', 8, 0), 111)

    still results in 20070101....

  • August 11, 2008 at 8:13 am

    #856098

    Hi VB,

    SELECT CAST('20070101' AS datetime)

    result: 2007-01-01 00:00:00.000

    Cath

  • August 11, 2008 at 8:18 am

    #856101

    thanks ive just tried that on my table and i get the following error:

    Arithmetic overflow error converting expression to data type datetime.

    Here is the SQL, which I have replaced the 20070101 to a field

    SELECT CAST(date_key AS datetime)

    FROM tbl_product

  • August 11, 2008 at 8:20 am

    #856102

    DECLARE@Source INT

    SET@Source = 20070101

    SELECT CONVERT(CHAR(10), CAST(STR(@Source, 8, 0) AS DATETIME), 111)

    N 56°04'39.16"
    E 12°55'05.25"

  • August 11, 2008 at 8:26 am

    #856107

    thanks peso that did the trick 🙂

Viewing 7 posts - 1 through 6 (of 6 total)

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