Having trouble converting to datetime in SQL import

Our database gets updated each week with text files spat out by a mainframe. Previously, the database was in Access; we copied and pasted the text files into Excel, ran macros on them to convert the data, then pasted the results directly into Access and this worked fine for us.

Now that we've moved the tables to SQL Server 2000, we're having problems with the data. We wanted to set up DTS packages for each file to just put them directly into SQL Server. This works for pretty much everything except for the dates.

The way the files are set up, they're comma delimited files with quotes around the text and nothing around the dates. The dates don't have any delimiters; they're just listed like 13012006. Every time we try to import these files into SQL, it gripes about the datatypes; we're trying to put the dates into datetime fields but SQL thinks they're strings. Eek! If we put date delimiters (like 13/01/2006) SQL pulls them in fine, but apparently the mainframe lacks the ability to put these delimiters in by itself and still run everything else OK. The person who writes the extracts has to do it in a language called 'Focus' which I've never heard of and don't know anything about, and he says what I'm asking for can't be done. OK...so now what?

I've tried and tried to convert these strings into dates using both CAST() and CONVERT() and just can't manage to do it. I know I'm missing something really obvious here; does anyone have any tips or advice? Thanks in advance.It's a bit of a pain to find in the DTS Designer, but within the Data Pump task, if you individually define the column mappings, there is an option to select DateTime strings.

One way to do this is to:
1. Select your source and destination connections
2. Add the Data Pump task
3. Specify your source table/query
4. Specify the destination query
5. Click on the 'Transformations' tab (usually the column mappings will auto-populate)
6. Click on the column mapping where the source is the date formatted as a string.
7. Delete this column mapping (click on delete or hit the delete key)
8. Select the source column and the destination column and click on 'New'
9. Choose the third item from the pop-up list (DateTime string)
10. You will then have to define the properties of the source and destination formats.

I've worked with this before and it worked pretty well.

Alternatively, you could proceed through item 8 and then (in item 9) choose ActiveX script. This will allow you to define more custom formats and insert additional error checking and/or handling.

Regards,

hmscott|||It's a bit of a pain to find in the DTS Designer, but within the Data Pump task, if you individually define the column mappings, there is an option to select DateTime strings.

One way to do this is to:
1. Select your source and destination connections
2. Add the Data Pump task
3. Specify your source table/query
4. Specify the destination query
5. Click on the 'Transformations' tab (usually the column mappings will auto-populate)
6. Click on the column mapping where the source is the date formatted as a string.
7. Delete this column mapping (click on delete or hit the delete key)
8. Select the source column and the destination column and click on 'New'
9. Choose the third item from the pop-up list (DateTime string)
10. You will then have to define the properties of the source and destination formats.

I've worked with this before and it worked pretty well.

Alternatively, you could proceed through item 8 and then (in item 9) choose ActiveX script. This will allow you to define more custom formats and insert additional error checking and/or handling.

Regards,

hmscott

Thanks for the advice; I'll try your first suggestion, since I can't write ActiveX script. I can usually manage SQL and I can almost always figure it out on my own, but this one stumped me. I'm not a certified DBA or anything, I'm still learning. :)|||It's a bit of a pain to find in the DTS Designer, but within the Data Pump task, if you individually define the column mappings, there is an option to select DateTime strings.

One way to do this is to:
1. Select your source and destination connections
2. Add the Data Pump task
3. Specify your source table/query
4. Specify the destination query
5. Click on the 'Transformations' tab (usually the column mappings will auto-populate)
6. Click on the column mapping where the source is the date formatted as a string.
7. Delete this column mapping (click on delete or hit the delete key)
8. Select the source column and the destination column and click on 'New'
9. Choose the third item from the pop-up list (DateTime string)
10. You will then have to define the properties of the source and destination formats.

I've worked with this before and it worked pretty well.

Alternatively, you could proceed through item 8 and then (in item 9) choose ActiveX script. This will allow you to define more custom formats and insert additional error checking and/or handling.

Regards,

hmscott

We tried using datetime strings and it just refused to work; we had to write ActiveX scripts n the end using DateSerial in VBScript, which was tough for us because neither one of us really knows VBScript, I'm a VB noob and my colleague knows VBA but not VBScript; I know they're basically the same but he doesn't. :) But we did eventually get it to work! So yay!