29

I am trying to convert a datetime (createTime) field from its default 2012-10-06 02:29:37.243 format to am/pm so I use

convert(varchar, CreateTime, 100) as CreateTime

and get Oct 6 2012 4:29AM.

So far so good but in the same statement I want to subtract 4 hours and then display the field (GMT issues).

I have gone through some posts here but could not find anything that can do it in single statement.

RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
bobsingh1
  • 393
  • 1
  • 3
  • 6

3 Answers3

48
declare @createTime datetime = '2012-10-06 02:29:37.243';

-- original value, default formatting
select @createTime;

-- formatted
select convert(varchar, @createTime, 100);

-- subtract 4 hours, formatted
select convert(varchar, dateadd(hour, -4, @createTime), 100);

The query above that uses dateadd will always subtract 4 hours. If your goal is to convert an arbitrary datetime from UTC to local time, then it's more complicated because the offset that you need to add/subtract depends on the original datetime. A single value like -4 won't always work. Here are some ideas for dealing with the general case:

Effectively Converting dates between UTC and Local (ie. PST) time in SQL 2005

Community
  • 1
  • 1
cbranch
  • 4,709
  • 2
  • 27
  • 25
2

For People Who wants to subtract days and hours

    declare @createTime datetime = '2012-10-06 02:29:37.243';
select @createtime as originaltime, dateadd(day, -4, dateadd(hour,-1,@createtime))as minus4daysandhour
mzonerz
  • 1,220
  • 15
  • 22
0

For SQL server you could use datetimeoffset

For mysql use CONVERT_TZ function to convert to a different timeszone.

Anshu
  • 7,783
  • 5
  • 31
  • 41