Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Converting dates to integer

Go down

Converting dates to integer Empty Converting dates to integer

Post  mkale 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

Back to top Go down

Converting dates to integer Empty Re: Converting dates to integer

Post  VHF 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

Back to top Go down

Converting dates to integer Empty it works!

Post  mkale Wed Sep 15, 2010 12:59 pm

thanks so much!

mkale

Posts : 2
Join date : 2010-09-14

Back to top Go down

Converting dates to integer Empty Re: Converting dates to integer

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top


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