While selecting date from databse I want to add 5:30 to created_at date in the database

Question

My Sql server existed in UTC. When I am selecting I want to get DATE to which +5:30 is added.

select cl.ChangeStatus,cl.created_at from LogTable cl join  Table t on cl.Id=t.ID;


ChangeStatus     created_at
----------------------------------
submitted   2019-01-10 08:03:11
sent        2019-01-10 08:06:55
sent        2019-01-10 08:08:14
sent        2019-01-10 08:09:03
complete    2019-01-10 08:09:54 

ChangeStatus   created_at
--------------------------------
submitted   2019-01-10 12:33:11
sent        2019-01-10 12:36:55
sent        2019-01-10 12:38:14
sent        2019-01-10 12:39:03
complete    2019-01-10 12:39:54

Is it mysql or sql server related? Remove one tag which is not related. — Suraj Kumar, Jan 10 '19 at 12:10
Sorry I added SQL Server by mistake, I'm currently using MySql — Kodali444, Jan 10 '19 at 12:20
If its Mysql, try this link : https://stackoverflow.com/questions/2187593/can-mysql-convert-a-stored-utc-time-to-local-timezone — Dheerendra, Jan 10 '19 at 12:22

score 0 · Answer 1 · edited Jan 10 '19 at 14:02

0

The following query should do the trick:

select
cl.ChangeStatus,
Todatetimeoffset(cl.created_at, '+5:30') as created_at
from LogTable cl
join  Table t on cl.Id=t.ID;

edited Jan 10 '19 at 14:02

Peter

8,776
6
62
95

answered Jan 10 '19 at 12:20

Dheerendra

284
1
7

While selecting date from databse I want to add 5:30 to created_at date in the database

1 Answers1