Thursday, March 29, 2012

converting datetime formats and layouts?

Opening DimTime table of AdventureWorksDW sample database in MS SQL Server Management Studio shows me values in FullDateAlternatKey like

01.07.2001 0:00:00

select fulldatealternatekey from dimtime
gives me the results like
2001-07-01 00:00:00.000

(1) Why is it?
(2)How can I "SELECT fulldatealternatekey" in different format like 9/3/2001 0:00
(that is given in SampleCurrencyData.txt ?
Well, I am trying to follow Integration Service Tutorial, Lesson1 (SQL Server 2005 Books Online) and have yet another format of datetime like 9/3/2001 0:00 in SampleCurrencyData.txt and receive the type mismatch in part "To add and configure the DateKey Lookup transformation" (8.In the Available Input Columns panel, drag CurrencyDate to the Available Lookup Columns panel and drop it on FullDateAlternateKey.)

Flat File Connection Manager Editor --> Advanced --> DataType gives me following formats:
- file_timestamp [DT_FILETIME]
- database date [DT_DBDATE]
- database time [DT_DBTIME]
- database timestamp [DT_DBTIMESTAMP]
- date [DT_DATE]
- file timestamp [DT_FILETIME]

(3)
BOL2005 give me general description of these date-times but how do I govern separation delimitoers in them (., /, -)?

That is the recommended way to store dates in SQL Server. The best way to represent dates in your application is to extract them as-is from the database, and format them using the application language's built-in string formatting functions. Here is a list of them for C# -http://blog.stevex.net/index.php/string-formatting-in-csharp/|||

Really, my problem has nothing to do with C# or storing dates by ME (?!) since I am trying to reproduce SSIS tutorial (as I mentioned it more specifically above).

I found some related posts to my problem:
http://forums.asp.net/thread/1688990.aspx
converting datetime formats and layouts?

ETL Package Problem
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=603488&SiteID=1


Though neither of tholutions helped me!

|||I see that I cited this same post.
Instead, one more post that describes the same problem bnut had not helped me is

collation or local sensitive settings or other configuration properties
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=180740&SiteID=1

No comments:

Post a Comment