Date Conversion

Question

Post reply

Date Conversion

rahulsahay123

Old Hand

Points: 398
More actions
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

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

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

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 1

John Mitchell-245523

SSC Guru

Points: 148786

January 8, 2016 at 4:06 am

#1850446

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

John

LinksUp SSCertifiable Points: 6526 More actions · Answer 2

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/

rahulsahay123 Old Hand Points: 398 More actions · Answer 3

rahulsahay123

Old Hand

Points: 398

January 8, 2016 at 7:11 am

#1850513

Thanks for the prompt reply!!

Jason A. Long SSC-Insane Points: 23731 More actions · Answer 4

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);

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 5

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;

johnwalker10 SSCrazy Eights Points: 9124 More actions · Answer 6

johnwalker10

SSCrazy Eights

Points: 9124

January 8, 2016 at 10:18 pm

#1850737

SELECT convert(varchar, getdate(), 102)

Eirikur Eiriksson SSC Guru Points: 182631 More actions · Answer 7

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));

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 8

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.

bgalway Ten Centuries Points: 1158 More actions · Answer 9

bgalway

Ten Centuries

Points: 1158

January 11, 2016 at 8:20 am

#1850977

Perhaps you can use DATEPART?

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 10

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]

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 11

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]

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 12

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]

Eric M Russell SSC Guru Points: 125255 More actions · Answer 13

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

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 14

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]