Converting dates to integer
2 posters
Page 1 of 1
Converting dates to integer
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.
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
Re: Converting dates to integer
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.
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
Similar topics
» Fact with eff / exp dates referencing dimension with eff / exp dates
» Converting Snowflake to Star
» Converting Columns into Rows.
» Staging and Highly Coupled Columns
» FACT : Begin and End Dates
» Converting Snowflake to Star
» Converting Columns into Rows.
» Staging and Highly Coupled Columns
» FACT : Begin and End Dates
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|