Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL code to extract 30mins from a varChar data type

Posted on 2010-09-22
12
Medium Priority
?
451 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:JCTDD
  • 4
  • 3
  • 3
  • +2
12 Comments
 
LVL 9

Expert Comment

by:rfportilla
ID: 33741102
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
 
LVL 9

Expert Comment

by:rfportilla
ID: 33741135
If you can standardize your date/time field, what you want to do is trivial.
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 33741215
select DATEADD(MINUTE,-30,YourTimeField) as Time1, YourTimeField as Time2
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50

Accepted Solution

by:
Lowfatspread earned 2000 total points
ID: 33741392
select substring(convert(varchar(22),DATEADD(n,-30,replace(YourTimeField,'.'.':')) ) ,13,8) as Time1, YourTimeField as Time2
0
 
LVL 2

Expert Comment

by:ben9
ID: 33747053
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
 
LVL 2

Expert Comment

by:ben9
ID: 33747103
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
 

Author Comment

by:JCTDD
ID: 33749685
thanks guy I will standardize the data first then apply your code
0
 

Author Comment

by:JCTDD
ID: 33749845
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
 

Author Comment

by:JCTDD
ID: 33749887
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
 
LVL 2

Expert Comment

by:ben9
ID: 33750796
JCTDD, I should have noticed SQL 2000. The above code was for SQL 2008. Sorry.
0
 

Author Comment

by:JCTDD
ID: 33750992
its ok I have used:

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

then followed above code
0
 
LVL 9

Expert Comment

by:rfportilla
ID: 33762293
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

963 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question