Home > Archive > Visual Basic Crystal Reports > January 2006 > How to convert this time conversion string to SQL code
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
How to convert this time conversion string to SQL code
|
|
| mark.fergel@bankofamerica.com 2006-01-23, 7:07 pm |
| I've got a column in my table containing military time as a string
(hey, I didn't set up the database <grin> ). I use the following code
in Crystal to output it as a date style format:
ctime(
left({FACILITATOR_APPOINTMENT.START_TIME},2) + ":" +
mid({FACILITATOR_APPOINTMENT.START_TIME},3,2) + ":" +
mid({FACILITATOR_APPOINTMENT.START_TIME},5,2))
I need to convert this over to SQL now.....
I've tried things like:
CONVERT(CHAR(24),FACILITATOR_APPOINTMENT
.START_TIME,108) "Start Time",
and
right(convert(char(10),FACILITATOR_APPOI
NTMENT.START_TIME,100),7)
"Start Time",
But it keeps coming back as a string. Does anyone know how I can
accomplish the same thing I did in crystal? Thanks in advance.....
| |
| José Araujo 2006-01-23, 7:07 pm |
| I think you are supposed to tell CONVERT the target type (datetime)... not
the source type (char).
José.
<mark.fergel@bankofamerica.com> wrote in message
news:1138033016.178019.156190@z14g2000cwz.googlegroups.com...
> I've got a column in my table containing military time as a string
> (hey, I didn't set up the database <grin> ). I use the following code
> in Crystal to output it as a date style format:
>
> ctime(
> left({FACILITATOR_APPOINTMENT.START_TIME},2) + ":" +
> mid({FACILITATOR_APPOINTMENT.START_TIME},3,2) + ":" +
> mid({FACILITATOR_APPOINTMENT.START_TIME},5,2))
>
> I need to convert this over to SQL now.....
>
> I've tried things like:
>
> CONVERT(CHAR(24),FACILITATOR_APPOINTMENT
.START_TIME,108) "Start Time",
>
> and
>
> right(convert(char(10),FACILITATOR_APPOI
NTMENT.START_TIME,100),7)
> "Start Time",
>
> But it keeps coming back as a string. Does anyone know how I can
> accomplish the same thing I did in crystal? Thanks in advance.....
>
| |
| mark.fergel@bankofamerica.com 2006-01-24, 7:07 pm |
| I had actually tried it as:
right(convert(datetime,FACILITATOR_APPOI
NTMENT.START_TIME,100),7)
"Start Time",
But it fails with an out of range date time value, most like because
there is not date (just a time) and it's trying to convert the string
into a date value.
| |
| José Araujo 2006-01-24, 7:07 pm |
| print convert( datetime , '18:00:00', 108 )
prints: Jan 1 1900 6:00PM
So I guess you only need to add the ':' in the correct positions.
José.
<mark.fergel@bankofamerica.com> wrote in message
news:1138115122.288425.51370@g47g2000cwa.googlegroups.com...
>I had actually tried it as:
>
> right(convert(datetime,FACILITATOR_APPOI
NTMENT.START_TIME,100),7)
> "Start Time",
>
> But it fails with an out of range date time value, most like because
> there is not date (just a time) and it's trying to convert the string
> into a date value.
>
| |
| mark.fergel@bankofamerica.com 2006-01-24, 7:07 pm |
| Got it......ended up having to do the following:
STUFF(STUFF(FACILITATOR_APPOINTMENT.START_TIME, 5, 0, ':'), 3, 0, ':')
'Start Time',
|
|
|
|
|