SQL convert datetime and subtract hours

问题:

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.

回答1:

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

回答2:

For SQL server you could use datetimeoffset

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

回答3:

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