Substring error datetime from charater string

Question

Post reply

Substring error datetime from charater string

Ayie

SSC Eights!

Points: 828
More actions
September 19, 2010 at 9:53 am

#229412

Hi
I'm having a problem with converting datetime from character string, with substring. Below is my code
This code is working when the string value is correct like this
20100916101010
insert into testing(TransactionDate) selectcast(
Substring(Convert(Varchar,date1),1,8)+' '+
Substring(Convert(Varchar,date1),9,2)+':'+
Substring(Convert(Varchar,date1),11,2)+':'+
Substring(Convert(Varchar,date1),13,2) as datetime) from testing1
but the character string value is like this
A000120034567820100916101010
insert into testing(TransactionDate) select cast(
Substring(Convert(Varchar,date1),15,8)+' '+
Substring(Convert(Varchar,date1),23,2)+':'+
Substring(Convert(Varchar,date1),25,2)+':'+
Substring(Convert(Varchar,date1),27,2) as datetime) from testing1
i am getting error below. Anyone can help me to this problem, please urgent
Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.
Thanks
Ayie

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

Please use convert(datetime, ...., format) when you want to handle datetime info.

Check books online for the correct format parameter to be used.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

LutzM SSC Guru Points: 107049 More actions · Answer 2

It seems like you have both types of values in that column.

Therefore, the first select will fail for the second type of data and vice versa.

If you always have the last 14 character of your string holding the datetime, you could use:

select cast(

Substring(Convert(Varchar,right(date1,14)),1,8)+' '+

Substring(Convert(Varchar,right(date1,14)),9,2)+':'+

Substring(Convert(Varchar,right(date1,14)),11,2)+':'+

Substring(Convert(Varchar,right(date1,14)),13,2) as datetime)

--or as an alternative

SELECT CAST(STUFF(STUFF(STUFF(RIGHT(date1,14),13,0,':'),11,0,':'),9,0,' ') AS DATETIME)

Btw: Why do you have a Convert() in your query? What is the original data type?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Ayie SSC Eights! Points: 828 More actions · Answer 3

Hi Lutz,

below is my data

insert into testing1(date1) values('A15014248336 00008800VIS20100907100000');

A15014248336 00008800VIS20100907100000EUR0USD0000000000000100000000000000000000100000000000000000000100000000000000000000100000000000000000000000000020000000000000000000000000000000000020000000000000000000000000000000000026500000000000000000000026500000000000000000000000000000209

I copy your scripts is working fine to my previous post, but my original data is at the above,

Im trying to change your scripts but i receive same error.

select cast(

Substring(Convert(Varchar,right(date1,32)),1,8)+' '+

Substring(Convert(Varchar,right(date1,32)),9,2)+':'+

Substring(Convert(Varchar,right(date1,32)),11,2)+':'+

Substring(Convert(Varchar,right(date1,32)),13,2) as datetime) from testing1

Please help me to resolve this issue.

thank you very much

Ayie

LutzM SSC Guru Points: 107049 More actions · Answer 4

Unfortunately, I don't know how your original data look like (you refer to "above" but you have two very different sample rows in that post.)

Some of your sample data have spaces, others don't, some are followed by additional data (all in one column... did you ever try to normalize it????)

Please post table def, sample data, business rules and expected result based on your sample data.

Right now, there seems to be no way to extract the date out of your very different sample data.

Therefore, I added the question regarding the business rules: you need to tell us, where or how to locate the date you want to convert.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 5

If there will always be a date somewhere in the data, or if you only want to process rows that have a valid date embedded in them, you can do this:

SELECT STUFF(STUFF(STUFF(SUBSTRING(date1,

PATINDEX('%[12][90][0-9][0-9][01][0-9][0123][0-9][01][0-9][0-5][0-9][0-5][0-9]%', date1)

, 14), 9, 0, ' '), 12, 0, ':'), 15, 0, ':') AS [date]

FROM (

SELECT '20100916101010' AS date1 UNION ALL

SELECT 'A000120034567820100916101010' UNION ALL

SELECT '00008800VIS20100907100000EUR0USD00000' +

'000000001000000000000000000001000000000000000000001' +

'000000000000000000001000000000000000000000000000200' +

'000000000000000000000000000000000200000000000000000' +

'000000000000000000265000000000000000000000265000000' +

'00000000000000000000000209' UNION ALL

SELECT REPLICATE('a', 100)

) AS testData

-- optional,

WHERE date1 LIKE '%[12][90][0-9][0-9][01][0-9][0123][0-9][01][0-9][0-5][0-9][0-5][0-9]%'

If there won't always be a date, you can do this:

SELECT STUFF(STUFF(STUFF(SUBSTRING(date1 + '19000101000000',

PATINDEX('%[12][90][0-9][0-9][01][0-9][0123][0-9][01][0-9][0-5][0-9][0-5][0-9]%', date1 + '19000101000000')