CODEWITHSUNDEEP
sqlsql-servert-sql
JC Borlagdan
JC Borlagdan

Reputation: 3638

How do I add Time in SQL?

So my data is 1:30 PM. I want to have a Data when user selects a time, I want it to add 1hr 30mins to that and make the data as 1:30 PM - 3:00 PM. I've been searching for an answer and tried some workarounds but i couldn't get it. BTW the datatype of my Time is Varchar, because when I tried using the Time datatype I had some issues on inserting from C# to SQL

Upvotes: 2

Views: 7962

Answers (2)

tarheel
tarheel

Reputation: 4797

--this does a few things
-- 1) converts the time stored as a varchar to the time datatype
-- 2) adds 90 minutes
-- 3) converts the time result back to the varchar datatype

select convert(varchar(10), dateadd(mi, 90, convert(time, '1:30 PM')), 100)


--this will show a final result of "3:00PM"

EDIT:

The following query will also return a varchar with the space between before the AM/PM. 

select format(dateadd(mi, 90, convert(datetime, '1:30 PM')), 'h:mm tt')

The 'h:mm tt' is written with one "h" so that it will show up as "3:00 PM", instead of "03:00 PM".

Using Replace:

This would be how I would use replace to get to the goal of "3:00 PM"

select replace(replace(convert(varchar(10), dateadd(mi, 90, convert(time, '1:30 PM')), 100), 'P', ' P'), 'A', ' A')

Upvotes: 1

chungtinhlakho
chungtinhlakho

Reputation: 930

tested with sql-server 2008 

 declare @t varchar(25)

    set @t = '1:30 PM'

    --conert a varchar to time
    select CONVERT(time, @t)

    --if you want to add 90 minutes to it

    select DATEADD(minute,90,CONVERT(time, @t))

Upvotes: 4

Related Questions