Dates in SQLSERVER SSIS and SSRS

Hi all,

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 ?


Thanks,
Maf.
LVL 6
MafaldaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

regbesCommented:
Hi Mafalda,

Most of your issuse can be resolved by understanding the dattime datatype and the convert function

MSSQL saves dates in the db as a number representing number of days from 1900/1/1 and the decimals showing seconds from 00:00 so what you see is just formatting issues
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_7msw.asp

to inseert a time stamp use the getdate() function

the convert function will enable displaying the stored date in diffrent formats
http://msdn2.microsoft.com/en-us/library/ms187928.aspx

In SSRS you should be able to use a calandar control to select the dates


Cheers!
MafaldaAuthor Commented:
Hi reqbes,

Thanks for your reply and links.
I am aware of the data types.
I might be able to use the CONVERT function to format the needed date when the input is YYMMDD ... but it wont save a lot, i will still need the process I posted.
Nevertheless I am asking here if there are ways to set up date formats in properties of SSIS and SSRS and save the trouble to convert things myself.
SSIS and SSRS gives you an interface that saves you tons of work but some things you cant do with it ... so im asking.
In addition I would like to change the VIEW behaviour of dates in the SqlServer tools and I dont know how to do it (e.g. see DD/MM/YYYY HH:MI:SS when I get a timestamp column after performing a query ... without formating it)  

Thanks again
Maf
regbesCommented:
>> YYMMDD ... but it wont save a lot, i will still need the process I posted.
yes unless you use a calander control

>>(e.g. see DD/MM/YYYY HH:MI:SS when I get a timestamp column after performing a query ... without formating it)

The default format displayed is derived from regional settings in control panel change it there and it should change in management console

as far as i remember SSRS has a option to use a setting on the server for all clients or to use the client setting

HTH

R.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.