I am using SQLSERVER 2005 SSIS and SSRS
I need to import, keep, query and present dates in several formats.
I have the following problems (not all presented here).
1) Although I chose a non-American ( HEBREW_CI_AS) collation for the database the datetime are displayed in the SqlServer Management Studio tools as MM/DD/YYYY HH:MI:SS although it should be DD/MM/YYYY HH:MI:SS
Does Collation set the date format ?
If not where can I set it ?
Can I control individual date columns ?
2) Using SSIS I'm inserting a date from a flat file in the fomat YYMMDD by reading it as DT_STR of lenght 6 and then using a Derive Column transformation that formats a new string SUBSTRING(3,2) + "/" + SUBSTRING(5,2) + "/20" + SUBSTRING(1,2) and using a Data Conversion transformation to convert it to DT_DBTIMESTAMP.
Is this the right process to do ?
Isn't there a way to format the date I'm reading and spare all this trouble ?
Again, I need to concatenate the needed date as MM/DD//20YY and not DD/MM/20YY to get the needed results.
I'm using a Derived Column Error Output to send bad records to a table in the database.
I would like all records with bad dates to go there.
I can trap bad DAY in dates like 061032 which is transform to 10/32/2006 (MM/DD/YYYY)
But I can't trap bad months ... 061610 and 061016 are both transformed into 10/16/2006 (MM/DD/YYYY) although 061610 should fail.
Can I control this behaviour ?
3) Using SSRS, I need to be able to control the dates behaviour as well.
I need to be able to present a date in a desired way and I need to be able to allow users to query the databae in either format MM/DD/YYYY or DD/MM/YYYY or any other format as needed.
Can I achieve this ?