Post reply

Date Conversion

  • January 8, 2016 at 3:55 am

    #306762

    I am getting a data feed with date column of type integer.Format is mmddyyyy.

    Values are like : 01092015

    Now i want to convert this into yyyy-mm-dd format ie 2015-01-09

    Pls suggest the way. Thanks in advance

    Rahul Sahay

  • January 8, 2016 at 4:06 am

    #1850446

    https://msdn.microsoft.com/en-GB/library/ms187928(v=sql.100).aspx

    John

  • January 8, 2016 at 4:33 am

    #1850452

    This is a naive way of doing it, but it works.

    declare @iDate int = 01092015

    select cast(substring(cast(@iDate+100000000 as char(11)), 6, 4) + substring(cast(@iDate+100000000 as char(11)), 2, 4) as date)

    __________________________________________________________________________________________________________
    How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • January 8, 2016 at 7:11 am

    #1850513

    Thanks for the prompt reply!!

  • January 8, 2016 at 2:52 pm

    #1850709

    rahulsahay123 (1/8/2016)

    I am getting a data feed with date column of type integer.Format is mmddyyyy.

    Values are like : 01092015

    Now i want to convert this into yyyy-mm-dd format ie 2015-01-09

    Pls suggest the way. Thanks in advance

    Rahul Sahay

    If the date looks like this... 01092015 it's not an integer. An integer would drop the leading 0. Most likely just text.

    In which case, this is all you need...

    DECLARE @Date CHAR(8) = '01092015';

    SELECT CAST(SUBSTRING(@Date, 5, 4) + SUBSTRING(@Date, 1, 2) + SUBSTRING(@Date, 3, 2) AS DATE);

  • January 8, 2016 at 4:48 pm

    #1850718

    Jason A. Long (1/8/2016)

    rahulsahay123 (1/8/2016)

    I am getting a data feed with date column of type integer.Format is mmddyyyy.

    Values are like : 01092015

    Now i want to convert this into yyyy-mm-dd format ie 2015-01-09

    Pls suggest the way. Thanks in advance

    Rahul Sahay

    If the date looks like this... 01092015 it's not an integer. An integer would drop the leading 0. Most likely just text.

    In which case, this is all you need...

    D E C L A R E @Date CHAR(8) = '01092015'; -- can't post the declaration

    SELECT CAST(SUBSTRING(@Date, 5, 4) + SUBSTRING(@Date, 1, 2) + SUBSTRING(@Date, 3, 2) AS DATE);

    Or this:

    with sampledata as (

    select '01092015' as SampleDate

    )

    select SampleDate, cast(right(SampleDate,4) + left(SampleDate,4) as date) ConvertedDate from sampledata;

  • January 8, 2016 at 10:18 pm

    #1850737

    SELECT convert(varchar, getdate(), 102)

  • January 10, 2016 at 8:00 am

    #1850821

    rahulsahay123 (1/8/2016)

    I am getting a data feed with date column of type integer.Format is mmddyyyy.

    Values are like : 01092015

    Now i want to convert this into yyyy-mm-dd format ie 2015-01-09

    Pls suggest the way. Thanks in advance

    Rahul Sahay

    Few things wrong here, numerical data types do not have leading zeros and there is no such thing as a date format for the date and datetime type data types. If you are importing the data then you would probably like to convert the integer to the data type date which is very simple, no need to do any kind of string conversion or manipulation.

    😎

    DECLARE @DATEINT INT = 9012016;

    SELECT

    DATEFROMPARTS(

    (@DATEINT % 10000 )

    ,(@DATEINT / 10000) % 100

    ,(@DATEINT / 1000000));

  • January 11, 2016 at 7:58 am

    #1850967

    Eirikur Eiriksson (1/10/2016)

    rahulsahay123 (1/8/2016)

    I am getting a data feed with date column of type integer.Format is mmddyyyy.

    Values are like : 01092015

    Now i want to convert this into yyyy-mm-dd format ie 2015-01-09

    Pls suggest the way. Thanks in advance

    Rahul Sahay

    Few things wrong here, numerical data types do not have leading zeros and there is no such thing as a date format for the date and datetime type data types. If you are importing the data then you would probably like to convert the integer to the data type date which is very simple, no need to do any kind of string conversion or manipulation.

    😎

    D E C L A R E @DATEINT INT = 9012016; -- can't post declarations from work.

    SELECT

    DATEFROMPARTS(

    (@DATEINT % 10000 )

    ,(@DATEINT / 10000) % 100

    ,(@DATEINT / 1000000));

    Won't work in SQL Server 2008.

  • January 11, 2016 at 8:20 am

    #1850977

    Perhaps you can use DATEPART?

  • January 11, 2016 at 8:42 am

    #1850986

    johnwalker10 (1/8/2016)

    SELECT convert(varchar, getdate(), 102)

    How can this work? Reread the OP's post.


    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • January 11, 2016 at 8:43 am

    #1850987

    Jason A. Long (1/8/2016)

    rahulsahay123 (1/8/2016)

    I am getting a data feed with date column of type integer.Format is mmddyyyy.

    Values are like : 01092015

    Now i want to convert this into yyyy-mm-dd format ie 2015-01-09

    Pls suggest the way. Thanks in advance

    Rahul Sahay

    If the date looks like this... 01092015 it's not an integer. An integer would drop the leading 0. Most likely just text.

    In which case, this is all you need...

    DECLARE @Date CHAR(8) = '01092015';

    SELECT CAST(SUBSTRING(@Date, 5, 4) + SUBSTRING(@Date, 1, 2) + SUBSTRING(@Date, 3, 2) AS DATE);

    Looks like something I would do.


    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • January 11, 2016 at 8:51 am

    #1850991

    Lynn Pettis (1/8/2016)

    Jason A. Long (1/8/2016)

    rahulsahay123 (1/8/2016)

    I am getting a data feed with date column of type integer.Format is mmddyyyy.

    Values are like : 01092015

    Now i want to convert this into yyyy-mm-dd format ie 2015-01-09

    Pls suggest the way. Thanks in advance

    Rahul Sahay

    If the date looks like this... 01092015 it's not an integer. An integer would drop the leading 0. Most likely just text.

    In which case, this is all you need...

    D E C L A R E @Date CHAR(8) = '01092015'; -- can't post the declaration

    SELECT CAST(SUBSTRING(@Date, 5, 4) + SUBSTRING(@Date, 1, 2) + SUBSTRING(@Date, 3, 2) AS DATE);

    Or this:

    with sampledata as (

    select '01092015' as SampleDate

    )

    select SampleDate, cast(right(SampleDate,4) + left(SampleDate,4) as date) ConvertedDate from sampledata;

    Looks like something else I'd do.


    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • January 11, 2016 at 8:54 am

    #1850993

    rahulsahay123 (1/8/2016)

    I am getting a data feed with date column of type integer.Format is mmddyyyy.

    Values are like : 01092015

    Now i want to convert this into yyyy-mm-dd format ie 2015-01-09

    Pls suggest the way. Thanks in advance

    Rahul Sahay

    Rather than MMDDYYYY, confirm if the data is instead using the pattern YYYYMMDD. That's the typical pattern for integer based date keys.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • January 11, 2016 at 8:59 am

    #1850996

    Eric M Russell (1/11/2016)

    rahulsahay123 (1/8/2016)

    I am getting a data feed with date column of type integer.Format is mmddyyyy.

    Values are like : 01092015

    Now i want to convert this into yyyy-mm-dd format ie 2015-01-09

    Pls suggest the way. Thanks in advance

    Rahul Sahay

    Rather than MMDDYYYY, confirm if the data is instead using the pattern YYYYMMDD. That's the typical pattern for integer based date keys.

    I certainly hope the sample data given (01092015) is not meant to be YYYYMMDD.


    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Viewing 15 posts - 1 through 15 (of 20 total)

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