Date Conversion

Question

Date Conversion

Viewing 6 posts - 16 through 20 (of 20 total)

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

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 1

Lynn Pettis (1/11/2016)
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.

Sorry, my bad, DATEFROMPARTS is 2012+, here is a 2008 and earlier version

😎

DECLARE @DATEINT INT = 9012016;

SELECT

DATEADD(YEAR, ((@DATEINT % 10000 ) - 1900 )

,DATEADD(MONTH,(((@DATEINT / 10000 ) % 100) - 1)

,DATEADD(DAY, (@DATEINT / 1000000) - 1,0)));

A word of caution though if using this code, it will not error if an invalid date is passed. On the other hand it is very fast.;-)

Edit: Caution

Eric M Russell SSC Guru Points: 125562 More actions · Answer 2

Alvin Ramard (1/11/2016)
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.

Hopefully 01092015 is not an actual sample data but just an illustration of what MMDDYYYY looks like. If the data type is interger, I doubt it would be an actual sample, because integers don't supply leading zeros.

"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 3

Eric M Russell (1/11/2016)
Alvin Ramard (1/11/2016)
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.
Hopefully 01092015 is not an actual sample data but just an illustration of what MMDDYYYY looks like. If the data type is interger, I doubt it would be an actual sample, because integers don't supply leading zeros.

The OP seems to be clear about the format, MMDDYYYY. I suspect he/she is dealing with numeric data in a string/text type field, but I could be wrong.

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: 125562 More actions · Answer 4

If you routinely encounter source data feeds containing dates formatted as strings (ie: MMDDYYY), then you may want to consider implementing a calendar reference table containing multiple variations of key codes that resolve to a proper Date typed column. In addition to resolving transformations by a simple join, it can also be leveraged for things like descriptive period rollups and identification of business days and holidays without cluttering SQL queries with parsing and data type conversion functions.

create table DimDate

(

CalDate date primary key not null,

YYYYMMDD char(8) not null,

MMDDYYYY char(8) not null,

DayNum tinyint not null,

MonthNum tinyint not null,

YearNum smallint not null,

MonthName varchar(20) not null,

DayOfWeekName varchar(20) not null,

FiscalQuarter tinyint not null,

IsBusinessDay tinyint not null,

IsHoliday tinyint not null

-- etc. etc.

);

create unique index ix_DimDate_YYYYMMDD on DimDate( YYYYMMDD );

create unique index ix_DimDate_MMDDYYYY on DimDate( MMDDYYYY );

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

MMartin1 One Orange Chip Points: 27799 More actions · Answer 5

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

If you are still introducing this into another string column then

declare @mystring varchar(10) = '01092015' ;

select right(@mystring,4) +'-'+

left(@mystring,2)+'-'+

substring(@mystring,3,2)asdateFormattedString

This leaves out the cast portion.

----------------------------------------------------

MMartin1 One Orange Chip Points: 27799 More actions · Answer 6

I am assuming that this is a string column for a int column would not hold the leading zero by the way.

declare @myint int = '0123'

select @myint

Plus needless to say I dont think you have this coming from a date column either.

----------------------------------------------------