0

I am using SQL Server 2014

Currently, I am using MSSQL function:

format(TODATETIMEOFFSET(("[dateColumn]",'-6:00'),'MM/dd/yyyy')

However, this will not apply Daylight savings.

How can i achieve it? Please help

Thom A
  • 88,727
  • 11
  • 45
  • 75
Sagar Chaudhary
  • 1,343
  • 7
  • 21

1 Answers1

2

This answer was provided before information was received that the OP was using SQL Server 2014 (which I have now added to their tags). I have left the answer here, however, as it will likely be helpful for future readers using a more recent version of SQL Server.

UTC -6 will always be UTC -6; it doesn't change for daylight savings and become UTC -5. Timezones like GMT (UTC+0) and EST (UTC-5) change to BST and EDT but then they also respectively become UTC +1 AND UTC -4. You're providing a literal number so the value will always be UTC -6.

What you are likely looking for is AT TIME ZONE (AT TIME ZONE (Transact-SQL), which was introduced with SQL Server 2016. For example:

--Returns 2018-01-01 18:00:00 -06:00
SELECT CONVERT(datetime2(0), '2018-01-01T18:00:00') AT TIME ZONE 'Central Standard Time'; 
--Returns 2018-07-01 18:00:00 -05:00 (changes to -5, due to daylight saving)
SELECT CONVERT(datetime2(0), '2018-07-01T18:00:00') AT TIME ZONE 'Central Standard Time';
Thom A
  • 88,727
  • 11
  • 45
  • 75
  • select format(CONVERT(datetime2(0),[CreatedOnDate]) AT TIME ZONE 'Central Standard Time','MM/dd/yyyy') as [CreatedOnDate], count(*) as [count] from [Buffer] group by format(CONVERT(datetime2(0),[CreatedOnDate]) AT TIME ZONE 'Central Standard Time','MM/dd/yyyy'); I have made the above query but it gives me an error, i.e Incorrect syntax near AT – Sagar Chaudhary Dec 04 '18 at 09:24
  • @SagarChaudhary To quote myself in my answer *"which was introduced with SQL Server 2016."* I would *suggest* you aren't using SQL Server 2016+? – Thom A Dec 04 '18 at 09:25
  • I am using SQL Server 2014. Is there any in SQL Server 2014? – Sagar Chaudhary Dec 04 '18 at 09:40
  • @SagarChaudhary `AT TIME ZONE` isn't available in SQL Server 2014, no. You'll need to likely create a "timezone table" or `Daylight savings table": A table which contains the start and ends dates of any (and all) daylight saving dates, by timezone. Then you'll need to join on to that and calculate accordingly. Not something I have time to explain/provide an example for at the moment I'm afraid. – Thom A Dec 04 '18 at 09:44
  • Thanks @Larnu for your time and explanation. I will look on the internet about that. – Sagar Chaudhary Dec 04 '18 at 09:47
  • @Larnu - feel free to update your answer using [this](https://github.com/mj1856/SqlServerTimeZoneSupport). Also, this was asked before and answered several times, but I can't find an *exact* duplicate to close with that matches the original question. The closest is probably [this one](https://stackoverflow.com/q/49231377/634824), or one linked from there. Feel free to answer this one focused on the specific question asked. ;) – Matt Johnson-Pint Dec 04 '18 at 16:30