page 1 of 1
Comments: 6 | Views: 636
#Aug 21st @ 9:23 AM
qwert231
qwert231
M Kenyon

First, I'm using data as it's given to me. I can't change the input or data structure, only my query out.

 

I have 3 fields, FeatureDate, time_start, and time_start_am_pm. FeatureDate is a datetime column, but has only dates, no times. The time is time_start, a varchar column, with the time formated as #### (ex. 7:00 PM would be 0700) and the time_start_am_pm column having either AM or PM.

 

I'm trying to join them all together into one datetime field.

 

So far, I've tried this, but it doesn't seem to work.

 CONVERT(datetime, CAST(CONVERT(varchar(4), DATEPART(mm, FeatureDate)) + '-'
     + CONVERT(varchar(2), DATEPART(dd, FeatureDate)) + '-'
     + CONVERT(varchar(4), DATEPART(yyyy, FeatureDate)) + ' '
     + CONVERT(char(2), LEFT(time_start,2)) + ':'
     + CONVERT(char(2), RIGHT(time_start,2)) + ' '
     + time_start_am_pm
     AS varchar(20)) ) AS DateTimeStart

 

Any suggestions?

#Aug 21st @ 9:25 AM
Matthew van Eerde
Matthew van Eerde
AKA Maurits

Looks like the right approach.  What error are you getting?  Try plugging in literals into your formula instead of the field names (skip the FROM clause), leaving off the CONVERT(datetime, ...) and see what you get.

#Aug 21st @ 9:37 AM
qwert231
qwert231
M Kenyon

Thanks for the help, if I do this (No Convert datetime):

CAST(CONVERT(varchar(4), DATEPART(mm, FeatureDate)) + '-'
     + CONVERT(varchar(2), DATEPART(dd, FeatureDate)) + '-'
     + CONVERT(varchar(4), DATEPART(yyyy, FeatureDate)) + ' '
     + CONVERT(char(2), LEFT(time_start,2)) + ':'
     + CONVERT(char(2), RIGHT(time_start,2)) + ' '
     + time_start_am_pm
     AS varchar(20))  AS DateTimeStart

 

I get this:

10-15-2008 07:30 PM

8-1-2009 08:00 AM
7-5-2009 09:00 AM
2-8-2009 08:00 AM

 

But with the CONVERT added, I get this message:

Msg 242, Level 16, State 3, Line 21

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

 

I tried specifying the style (mm/dd/yy) like this:

CONVERT(datetime, CAST(CONVERT(varchar(4), DATEPART(mm, FeatureDate)) + '-'
     + CONVERT(varchar(2), DATEPART(dd, FeatureDate)) + '-'
     + CONVERT(varchar(4), DATEPART(yyyy, FeatureDate)) + ' '
     + CONVERT(char(2), LEFT(time_start,2)) + ':'
     + CONVERT(char(2), RIGHT(time_start,2)) + ' '
     + time_start_am_pm
     AS varchar(20)), 100 ) AS DateTimeStart

But go this:

Msg 241, Level 16, State 1, Line 21

Conversion failed when converting datetime from character string.

#Aug 21st @ 11:26 AM
figuerres
figuerres
???

see what happens if you just play with the date.

 

depending on the server, if the datetime settings are not what you expect it might be thinking day-month-year so 10-15-2009 would not be valid.

 

there are sql server options for datetime that can override the os settings.

or if this is a linked oracle setup ... seems i recall you have to use oracle sometimes.... 

also you may be bale to simplify that sql.

if you know that the featuredate is always adate with no time then use this:

 

DATEADD (datepart , number, date )

i think you could convert your hhmm to an int # of minutes and then just do one add and then you skip some cast and convert calls.

 

#Aug 21st @ 11:32 AM
lensman
lensman

Why not do something like:

 

SELECT DATEADD(MINUTE, ((CAST(time_start as int) / 100) * 60) + (CAST(time_start as int) % 100) + CASE WHEN time_start_am_pm = 'PM' THEN (12 * 60) ELSE 0 END, FeatureDate) AS DateTimeStart

 

This simply adds the number of minutes specified in "time_start" to the original date.  It then adjusts the time by 12 hours based upon the AM/PM flag.  Obviously this does not take into considerations for NULL values or invalid times such as "0799".

 

 

#Aug 21st @ 12:42 PM
qwert231
qwert231
M Kenyon

Ah, yes, that works thank you.

#Aug 21st @ 3:18 PM
Matthew van Eerde
Matthew van Eerde
AKA Maurits

yyyy-mm-dd should always be safe.

page 1 of 1
Comments: 6 | Views: 636
Microsoft Communities