Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 452
  • Last Modified:

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

0
JCTDD
Asked:
JCTDD
  • 4
  • 3
  • 3
  • +2
1 Solution
 
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
 
Reza RadCommented:
select DATEADD(MINUTE,-30,YourTimeField) as Time1, YourTimeField as Time2
0
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.

 
LowfatspreadCommented:
select substring(convert(varchar(22),DATEADD(n,-30,replace(YourTimeField,'.'.':')) ) ,13,8) 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

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 4
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now