SQL datetime and time zone

Posted by Ksuresh under Sql Server on 2/5/2013 | Points: 10 | Views : 1409 | Status : [Member] | Replies : 4
Hi,

Sandeepmhatre posted this question in interview Experience Section but it should be in this forums, so i just posted his question here.


I had asked one question related datetime and timezone in a interview

Let me consider one example to explain:
suppose you are in American users add transaction entries in Web based application,
let assume entry punched @ 11.30pm on date 04 Feb 2013

Another user views report, which is located in India.
when he filters this report based on date he can not found the above entry ,

when he adds a date as 5 Feb 2013 user in india can view the entry ?

How to handle in sql please explain


Thanks in advance




Responses

Posted by: Blessyjees on: 2/5/2013 [Member] Bronze | Points: 25

Up
0
Down
Hi,

Your query is not fully clear. Do you want to see this date on the report user select the date 5 Feb 2013?

Blessy Baby
Digitalmesh Softech pvt Ltd
https://blessybaby.wordpress.com/

Ksuresh, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ksuresh on: 2/5/2013 [Member] Starter | Points: 25

Up
0
Down
Hi Blessyjees,

i think it is like this

One web application is there

American user purchased one product on 4 FEB 2013 11:30 pm

So now in India report manager seeing product orders, so same above time should be appear according Indian time zone in data table so how to handle using sql.

Regards
Suresh

Ksuresh, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pandians on: 2/5/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
It may be...

1. Always store DateTime in a specific format (i.e: SQL Server in PST format , Pls don't convert and store)
2. Use a Lookup table having Country, TimeZone, Offset value
3. Based on the request given from the country match with the Lookup table and convert the DateTime using "SWITCHOFFSET" and "datetimeoffset" in sql server! (Manually convert the DateTime using the Offset value if your sqls erver version is not support the Functions/DataType given above)


Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Ksuresh, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Arul44ece on: 2/5/2013 [Member] Starter | Points: 25

Up
0
Down
Hi Suresh ,

U can try using SWITCHOFFSET in SQL for above your query.




Regards,

Arul R
arul44.ece@gmail.com

Ksuresh, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response