July 13, 2009 at 9:51 am
I normally find my answer by going through your posts but this for this one I could use a helping hand.
I have a VarChar string (16). The field name is [DateTime].
A sample value: 2006042714245100
I am trying to accomplish the following:
SELECT convert(datetime,[datetime],113) from MyTable
I get the following error message:
Conversion failed when converting datetime from character string.
Could the problem stem from the field name? DateTime
Regards
July 13, 2009 at 11:27 am
It's not a valid datetime format for SQL Server. You'll need to massage the string before converting to a datetime. Something like this (assumes you always have 16 characters):
DECLARE @date_sting VARCHAR(16)
SET @date_sting = '2006042714245100'
SELECT
CONVERT(DATETIME, SUBSTRING(@date_sting, 1, 8)) AS date_part,
SUBSTRING(@date_sting, 9, 2) AS hour_part,
SUBSTRING(@date_sting, 11, 2) AS minute_part,
SUBSTRING(@date_sting, 13, 2) AS second_part,
RIGHT(@date_sting, 3) AS milliseconds_part,
DATEADD(Hour, CONVERT(INT, SUBSTRING(@date_sting, 9, 2)),
CONVERT(DATETIME, SUBSTRING(@date_sting, 1, 8))) AS Date_And_Hour,
DATEADD(Minute, CONVERT(INT, SUBSTRING(@date_sting, 11, 2)),
DATEADD(Hour, CONVERT(INT, SUBSTRING(@date_sting, 9, 2)),
CONVERT(DATETIME, SUBSTRING(@date_sting, 1, 8)))) AS Date_Hour_Minute
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 13, 2009 at 12:09 pm
Thanks for your help. I do appreciate this forum
Regards
Marc
July 13, 2009 at 1:12 pm
select
a.DT_In,
DT_Out =convert(datetime,stuff(stuff(stuff(stuff(a.DT_In,15,0,'.'),13,0,':'),11,0,':'),9,0,' '))
from
( --Test Data
select DT_In = '2006042714245123'
) a
Results:
DT_In DT_Out
---------------- ------------------------------------------------------
2006042714245123 2006-04-27 14:24:51.230
(1 row(s) affected)
July 13, 2009 at 1:15 pm
ARRRRGH! I always forget about STUFF().
I was also taught it was not polite to tell someone to "Stuff it" 😀
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 13, 2009 at 1:27 pm
Hello again Jack,
Here is the statement you helped to create. Thanks
SELECT datediff(mi, DATEADD(Second, CONVERT(INT, SUBSTRING(datetime1, 13, 2)),
DATEADD(Minute, CONVERT(INT, SUBSTRING(datetime1, 11, 2)),
DATEADD(Hour, CONVERT(INT, SUBSTRING([datetime], 9, 2)),
CONVERT(DATETIME, SUBSTRING(udl35, 1, 8))))), DATEADD(Second, CONVERT(INT, SUBSTRING(udl35, 13, 2)),
DATEADD(Minute, CONVERT(INT, SUBSTRING(udl35, 11, 2)),
DATEADD(Hour, CONVERT(INT, SUBSTRING(udl35, 9, 2)),
CONVERT(DATETIME, SUBSTRING(udl35, 1, 8)))))) from ddata
July 13, 2009 at 1:33 pm
I guess I pressed the wrong key. The reply posted before is not complete
Here goes again. It's kindda typing please read attached in an email without actually including the attachement.
SELECT datediff(mi, DATEADD(Second, CONVERT(INT, SUBSTRING(datetime1, 13, 2)),
DATEADD(Minute, CONVERT(INT, SUBSTRING(datetime1, 11, 2)),
DATEADD(Hour, CONVERT(INT, SUBSTRING(datetime1, 9, 2)),
CONVERT(DATETIME, SUBSTRING(datetime1, 1, 8))))),
DATEADD(Second, CONVERT(INT, SUBSTRING(datetime2, 13, 2)),
DATEADD(Minute, CONVERT(INT, SUBSTRING(datetime2, 11, 2)),
DATEADD(Hour, CONVERT(INT, SUBSTRING(datetime2, 9, 2)),
CONVERT(DATETIME, SUBSTRING(datetime2, 1, 8)))))) from MyTable
Thanks
Works like a charm.
Marc
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply