Solved

SQL code to extract 30mins from a varChar data type

Posted on 2010-09-22
12
444 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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
 

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

Technology Partners: 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!

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
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.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

623 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