Converting dates to integer

View previous topic View next topic Go down

Converting dates to integer

Post  mkale on Tue Sep 14, 2010 5:17 pm

I am in the process of building a datawarehouse. The OLTP has dates like "date_enforced", "date_entered" in the datetime format. How do I convert this to integer in SSIS since my date dimension has the datekey as integer in the YYYYMMDD format.
I am pulling from about 7 OLTP tables with about 4 date fields in each table.

Thanks in advance for any input.

mkale

Posts : 2
Join date : 2010-09-14

View user profile

Back to top Go down

Re: Converting dates to integer

Post  VHF on Wed Sep 15, 2010 12:22 pm

Here's how I convert from a date in SQL Server to a YYYYMMDD key:

CONVERT(int,CONVERT(varchar(8),[SaleDate],112)) AS [SaleDateKey]

The 112 specifies YYYYMMDD format.

In SSIS, you could put this in the SQL for your data source extract, so it would already be an integer by the time it got into SSIS. I'm sure one could also write a function in SSIS to do the date-to-integer transformation, but I've always just handled it in T-SQL.




VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

it works!

Post  mkale on Wed Sep 15, 2010 12:59 pm

thanks so much!

mkale

Posts : 2
Join date : 2010-09-14

View user profile

Back to top Go down

Re: Converting dates to integer

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum