Solved

SQL code to extract 30mins from a varChar data type

Posted on 2010-09-22
12
412 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
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now