Board index » delphi » Syntax error converting datetime from character string (MS SQL 2K)

2005-02-10 06:48:43 AM
delphi212
Hi to all!
Has anyone come across this error :"Syntax error converting datetime from
character string." with SQL server? Here's my dilemma:
I'm porting a legacy application to Delphi 7 with MS SQL as the backend, when I
tried to import a record from the legacy tables that happens to have a
datetime corresponding column on the SQL side, I get this error. The data
coming in from the legacy file is formatted as YYYYMMDDHHMMSS. I have tried
every possible format I have come across, but still get the error.
I tried these formats:
MM/DD/YY HH:MM:SS [AM/PM] - Did Not Work
MM-DD-YY HH:MM:SS [AM/PM] - Did Not Work
YYYY-MM-DD HH:MM:SS [AM/PM] - Did not work either
I wrote a program that reads the records in the legacy table, and then tries
to insert them into the SQL table. I need to preserve the time portion and
I don't want to add a new column to the SQL table to hold this value.
Can anyone help me?
 

This works in MS-SQL 2k
CREATE TABLE #TEMP1 (dt datetime)
DECLARE @str varchar(14)
Set @str = '20050209165300' --YYYYMMDDHHMMSS
PRINT SUBSTRING(@str,1,4) + '-' + SUBSTRING(@str,5,2) + '-' +
SUBSTRING(@str,7,2) + ' ' + SUBSTRING(@str,9,2) + ':' +
SUBSTRING(@str,11,2) + ':' + SUBSTRING(@str,13,2)
INSERT INTO #TEMP1 (dt) VALUES (CONVERT(datetime, SUBSTRING(@str,1,4) + '-'
+ SUBSTRING(@str,5,2) + '-' + SUBSTRING(@str,7,2) + ' ' +
SUBSTRING(@str,9,2) + ':' + SUBSTRING(@str,11,2) + ':' +
SUBSTRING(@str,13,2) ,120))
PRINT @@ROWCOUNT
Note I didn't use STUFF (I love STUFF!) because I couldn't figure out how to
use it without a function or cursoring.
 

Check in TransactSQL:
SET DATEFORMAT dmy
or (and)
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
where style is a number regarding the datetimeformat (see TransactSQL-Help)
Heinrich
Juan Rosique schrieb:

that does not include the time portion. The rest of the styles require some
form of punctuation.
 

Thank to you Patrick and Heinrich. I found the problem later on the day. I
had one column with bad data. After I corrected the issue I was able to
bring in all the records without any hiccups.
Thank you guys! You Rock!
"Juan Rosique" <XXXX@XXXXX.COM>writes