Data Type Conversion

[mariayx]

Hi all,
I need to parse a char(97) string using substring and convert it into a date. The following substring is either in 'yyyymmdd' format or a null.
SUBSTRING(@data_string, 54, 8)
I tried CAST to convert it to a date. But it said "Syntax error converting datetime from character string".
Please help. Thanks!

mariayx

 

[Bygbobbo]

I convert with the same yyyymmdd character format all the time.

convert(datetime,SUBSTRING(@data_string, 54, 8))

the only way this will fail is if the date was not a legitimate date.

For Example 20030832

If that doesnt work I have another method.

hope this helps..
Bygs

 

[hneal98]

You could do something like this:

case when ISDATE(SUBSTRING(@data_string, 54, 8))=1 then... else ...

Isdate will return a 1 if it is a date, otherwise it will return a 0.

 

[mariayx]

Thanks for your response.
I changed it to convert(datetime,SUBSTRING(@data_string, 54, 8)), the procedure got created with out errors.
So I ran the procedure with the case that the substring is a NULL there. It looks like this:
set @data_string = '383430473 E '
It still gave me the same error message. Any idea why?
I appreciate your help.
mariayx

 

[hneal98]

If you are just using a case statement and substring to test for null, that would probably be the problem. Try the example I showed in my previous response.

 

[mariayx]

In my case the substring could either be a null or a string of 'yyyymmdd' format. Error returned:Syntax error converting datetime from character string.

Here is part of the code.
set @l_Submit_Date = SUBSTRING(@data_string, 54, 8)
set @l_Date_Updated = SUBSTRING(@data_string, 62, 8)

IF ISDATE(@l_Submit_Date) = 1 BEGIN
SET @Submit_Date = CONVERT(DATETIME, @l_Submit_Date)

END
ELSE BEGIN
SET @Submit_Date = NULL
print 'In this case, the submit date is null.'
print 'The value should be null:' + @Submit_Date

END
Thanks,
mariayx

 

[hneal98]

If you are using the substring to pull data from a string, how could it be a null, unless the field is null in the first place? Is that what is happening?

I would do something like this:

IF ISDATE(isnull(@l_Submit_Date,'')) = 1
BEGIN
SET @Submit_Date = CONVERT(DATETIME, @l_Submit_Date)
END

Note that I put the isnull inside the isdate. If the string is null, it will add a blank and therefore will not be ignored.

 

[mariayx]

It worked with ISNULL. Thanks a lot for your help! Have a nice weekend.
mariayx

 

[hneal98]

You too.