SQL code to extract 30mins from a varChar data type

I have a column called presentationTime that is of a varChar(10) data type the aim is to use SQL to extract 2 columns

col1 - 30mins before the presentationTime
col2 - presentationTime

Table Name = Presentation
Presentation tables data
9.30am
3:00pm
11:00am


Need SQL to produce this output? Note some of the times entered use a decmial point instead of a collon between hours and minutes e.g. the first row below
Time1    Time2
9.00am   9:30am
2:30pm   3:00pm
10:30am  11:00am

JCTDDAsked:
Who is Participating?
 
LowfatspreadConnect With a Mentor Commented:
select substring(convert(varchar(22),DATEADD(n,-30,replace(YourTimeField,'.'.':')) ) ,13,8) as Time1, YourTimeField as Time2
0
 
rfportillaCommented:
This is a problem.  You need to standardize your data.  There is a datatype specifically for datetime: http://msdn.microsoft.com/en-us/library/aa258277(v=SQL.80).aspx
0
 
rfportillaCommented:
If you can standardize your date/time field, what you want to do is trivial.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
Reza RadConsultant, TrainerCommented:
select DATEADD(MINUTE,-30,YourTimeField) as Time1, YourTimeField as Time2
0
 
ben9Commented:
Date time style 0 seems to work:

declare @mytime varchar(20)
set @mytime = '12:00PM'

select convert(varchar(30),dateadd(minute,-30, cast(replace(@mytime,'.',':') as time)),0), replace(@mytime,'.',':')


select convert(varchar(30),dateadd(minute,-30, cast(replace(presentationTime,'.',':') as time)),0), replace(presentationTime,'.',':')
0
 
ben9Commented:
Actually varchar(30) is not right - it can just be varchar(10).

If the wrong format is fixed (9.30 -> 9:30), then it becomes

select convert(varchar(10),cast(dateadd(minute,-30,presentationTime) as time),0), presentationTime
from Presentation
0
 
JCTDDAuthor Commented:
thanks guy I will standardize the data first then apply your code
0
 
JCTDDAuthor Commented:
after I standardized the data e.g. (9.30 -> 9:30) and tried this:

select convert(varchar(10),cast(dateadd(minute,-30,presentationTime) as time),0), presentationTime
from Presentation

get err: Server: Msg 243, Level 16, State 1, Line 1
Type time is not a defined system type.

do you know how to fix?

0
 
JCTDDAuthor Commented:
I am using SQL 2000

i tried using datetime instead of time it just lists what ever is in the table and adds a date field

(no column name) | presentationTime
Jan  1 190                 | 9:00am
Jan  1 190                 | 11:00am
Jan  1 190                 | 3:00pm
0
 
ben9Commented:
JCTDD, I should have noticed SQL 2000. The above code was for SQL 2008. Sorry.
0
 
JCTDDAuthor Commented:
its ok I have used:

set dateformat dmy
select cast (presentationTime as datetime) as presentationTime
into #TimeAsDateTime
from [time]

then followed above code
0
 
rfportillaCommented:
I spoke hastily in my first comment, but I still strongly recommend that you update this field so that it uses the standard datetime format.  Future operations will be easier.  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.