DTS Date Formats

[IanMackay123]

Hi, I'd be really grateful if Someone could help me with this:

I have two tables, each with Datetime and boolean fields.

I've set up an automated repeating DTS for each table to output the data into CSV files.

When I check the CSV files in notepad the dates and Booleans are output in different formats for each table.

I need the outputs to be the same, but I wasn't aware the SQL Server could hold dates and booleans in different formats - Where can I change this setting? is it a DTS setting? I'm afraid I have not had much experience with DTS.

Thanks for any help!

Ian

 

[AlexCuse]

I'd bet that you have different date formats in your queries that are being exported (or some dates stored as text). I believe sql's default for a datetime colum is 'YYYY-MM-DD HH:MM:SS.XXX'.

Depending on how your transform is set up, there are two ways you can handle this. If its' a vbScript transformation you can use Format(DateValue, "YYYYMMDD") (or another format string) to get it into the desired format. If its' a straight column to column, you can use the convert function to get your date into the desired format (in your query being exported). Here is a article on date/time formatting in SQL Server:

http://sqljunkies.com/HowTo/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk

Hope this helps,

Alex

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[IanMackay123]

Sorry, I don't think I have the option of looking at the SQL queries (or I don't know how to...)

I'm using SQL server 2000 and have set up a recurring DTS package through enterprise manager DTS import/export wizard.

This creates a package in DTS/local packages, but I don't see how to actually get at any code behind the graphic interface. if I could then I could easily add a convert function in the right place.

Do you have any further advice? Any is appreciated!

Thanks,

Ian

 

[IanMackay123]

Ah, hang on, I've found the query builder section now - I'll have a crack at that.

Thanks for the help!

Ian