asked on

varChar Time values converted to DateTime to get 24hr time

Following on from my other questions "SQL code to extract 30mins from a varChar data type"

I can get the results I am after the code is a bit messy wondering if it can be done in one chunk without using a temp table.

running this select SeminarTime from dbo.SeminarSMS gives this Output:

SeminarTime
9:30am
12:00pm
3:00pm
4:00pm
9:00am

running the below code gives the desired results just wondering if it could be written better and without using a temp table:
set dateformat dmy
select cast (SeminarTime as datetime) as SeminarTime
into #TimeAsDateTime
from dbo.SeminarSMS
Select
CONVERT(VARCHAR(5)
, dateadd (minute, - 30, SeminarTime), 108) as Time1
, CONVERT(VARCHAR(5), SeminarTime, 108)            as Time2
from #TimeAsDateTime

result:
Time1 Time2
09:00      09:30
11:30      12:00
14:30      15:00
15:30      16:00
08:30      09:00

appari

try this

Select
CONVERT(VARCHAR(5)
, dateadd (minute, - 30, cast (SeminarTime as datetime) ), 108) as Time1
, CONVERT(VARCHAR(5), cast (SeminarTime as datetime) , 108) as Time2
from dbo.SeminarSMS

javaboon

You can write the whole thing as one query, no need for a temp table at all.

set dateformat dmy
Select
CONVERT(VARCHAR(5)
, dateadd (minute, - 30, cast (SeminarTime as datetime)), 108) as Time1
, CONVERT(VARCHAR(5), cast (SeminarTime as datetime), 108) as Time2
from dbo.SeminarSMS

cyberkiwi

You don't even need the first CAST

Select
CONVERT(VARCHAR(5), dateadd (minute, - 30, SeminarTime), 108) as Time1,
CONVERT(VARCHAR(5), cast(SeminarTime as datetime), 108) as Time2
from tbl

ASKER CERTIFIED SOLUTION

cyberkiwi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial