SQL Server Date Format India to US

I've a service which push date from my SQL server database to web database and the date format that is pushed is "yyyyMMdd".

My database resides in india, so date format and current date is based on Indian Culture and Locale based.

And my web server resided in US, so there the date format is based开发者_JS百科 on US.

There is almost 13 hours difference in Indian and US time. So when my client entered a consignment on say 13 June 2011 9:00 am in the morning, it shows on website that is received on 12th June.

So receiving a consignment before client even booked them makes such a big hassle. Please guide me for something to reolve this.

Thanks

This will take the user input and convert it to UTC:

DECLARE @input DateTime = '13 June 2011 9:00 am';
DECLARE @utc DateTime = DATEADD(s, DATEDIFF(s, GETDATE(), GETUTCDATE()), @input);

SELECT @input UserInput, @utc UTC;

You can store all DateTime values in your database as UTC and then present them to the user in whichever timezone / culture is appropriate.

The transaction did actually happen on the 12th of June in the USA, so that is actually correct.

If you want that date to show 12th of june when viewed in the USA and the 13th of June when viewed in India you need to store your dates as UTC dates.