Insert Query with Date Conversion Problem

[toekneel]

I'm rather new to SQL, and am working at upsizing a database from MS Access 2000. I was able to get the syntax right in Access for this problem, but I'm struggling with getting it right in SQL.

We have a text file that gets FTP'd nightly from a mainframe application. This is then imported into a table called tblErod. In order to bring it there, the text data type must be converted into a date-formatted field.

I've attempted working with both CAST and CONVERT. When I run it in the query analyzer, if I comment out the "INSERT INTO" statement, it runs without a problem. When I run it without commenting out that line, I receive this error: Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

Here's the dirty details:

This is what is used in MS Access 2000 that works:

INSERT INTO tblErod (field names) IN (MSAccess table location)
SELECT DateValue(Mid([strDateOpen],6,2) & "/" & Mid([strDateOpen],9,2) & "/" & Left([strDateOpen],4)) AS DateOpen1, DateValue(Mid([strDateCalled],6,2) & "/" & Mid([strDateCalled],9,2) & "/" & Left([strDateCalled],4))+TimeValue(Mid([strDateCalled],12,2) & ":" & Mid([strDateCalled],15,2)) AS Date2

I've converted it into SQL to this point:

INSERT INTO tblErod (field names)
SELECT CONVERT(DATETIME,Substring(strDateOpen,6,2) + '/' + Substring(strDateOpen,9,2) + '/' + Left(strDateOpen,4),101) AS dtmDateOpen, CONVERT(DATETIME,Substring(strDateCalled,6,2) + '/' + Substring(strDateCalled,9,2) + '/' + Left(strDateCalled,4)+ ' ' +
Substring(strDateCalled,12,2) + ':' + Substring(strDateCalled,15,2),101)AS dtmDateCalled,
FROM trwErod LEFT OUTER JOIN trwErodComments ON trwErod.strErodNum = trwErodComments.strErodNum

 

[cbokowski]

I just had to do something close to that.

try this.

Cast(Substring(strDateOpen,6,2) + '/' + Substring(strDateOpen,9,2) + '/' + Left(strDateOpen,4) as datetime)

 

[jhall156]

Jeez it looks good to me. Can you show us some of the result set without the insert. I started out with nothing, and I still have most of it.

 

[toekneel]

I've tried working with the CAST command with no better results.
Here's sample data that are retrieved; two separate fields, one is converting on date without time posted, the other with time posted. Just wondering... is the 00 time stuff what's messing me up?

2001-10-22 00:00:00.000; 2001-10-19 14:55:00.000
2001-10-22 00:00:00.000; 2001-10-19 16:25:00.000
2001-10-22 00:00:00.000; 2001-10-20 14:55:00.000
2001-10-22 00:00:00.000; 2001-10-22 07:15:00.000

 

[jhall156]

It shouldn't make a difference but isn't style 101 mm/dd/yy while style 100 on the other hand is mm/dd/yyyy. I tested it on my end and it inserted cleanly into one of my test tables. Have you verified the table column datatype? I started out with nothing, and I still have most of it.

 

[toekneel]

When I change the style from 101 to 100, I get the following error message:
Server: Msg 241, Level 16, State 1, Line 2
Syntax error converting datetime from character string.

The receiving table in this instance has a data type of datetime.

 

[tlbroadbent]

Convert the entire date tiem without using substrings by using the style number 121 - ODBC with ms (yyyy-mm-dd hh:mi:ss:mmmm).

Convert(datetime, strDateOpen, 121)
Convert(datetime, strDateCalled, 121) Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[toekneel]

In doing that, I get another error message:
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.

 

[tlbroadbent]

Are you sure all the input data is properly formatted? Run a query to verify that the columns actually contain datetime data.

Select * From Table
Where isdate(strDateOpen)=0
Or isdate(strDateCalled)=0 Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[toekneel]

All of the rows in the text file are not date formatted (which is the problem I'm trying to correct). Would it help for me to create a new temporary table to import the formatted items into and then test to see if they are really date-formatted after whatever conversion I apply? I'm assuming that problem I'm experiencing is that they are not successfully converting from the text file into Date-Formatted items.

Thanks for your input!

 

[toekneel]

I've just kind of stumbled onto something that seemed to work... although I'm not certain why at this point. While testing some things, instead of updating the existing table from the query, I created a new temporary table. I wanted to see if the outputted fields were the same as the target table. They were, but it still would not input it. Then I attempted to input the temporary table, and viola! Success! I can set up a proc to do all of this, but I'm still curious as to the "proper" way of doing it, and why this didn't work to bring it straight into the other table.

 

[tlbroadbent]

You could try something like the following. If the date column is valid insert the date else insert a NULL.

INSERT INTO tblErod (field names)
SELECT
Case IsDate(strDateOpen)
When 1 Then strDateOpen
Else Null End,
Case IsDate(strDateCalled)
When 1 Then strDateCalled
Else Null End,
<more field names>

FROM trwErod
LEFT OUTER JOIN trwErodComments
ON trwErod.strErodNum = trwErodComments.strErodNum Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.