Date Conversion

Question

Post reply

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: 182631 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: 125255 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: 125255 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: 27655 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)	as	dateFormattedString

This leaves out the cast portion.

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

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

MMartin1

One Orange Chip

Points: 27655

January 12, 2016 at 4:45 pm

#1851447

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.

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