September 28, 2012 at 8:57 am
I have a web application where the users will be entering time based on the different time zones there are in e.g. EST, CST, MST,PST etc...
My server is in CST so I need to convert the time to CST before storing it to a table.
I am using MSSQL 2008. Any help woul be greatly appreciated.
e.g.
@str_Date = '08/12/2012',
@str_time = '10:30',
@str_AMPM = 'PM'
@str_Tzone = 'EST'
This need to get stored as '08/12/2012 22:30:00.000'
I pretty much got the date and time format part but how to I convert into CST from the EST time?
September 28, 2012 at 9:16 am
Guras (9/28/2012)
I have a web application where the users will be entering time based on the different time zones there are in e.g. EST, CST, MST,PST etc...My server is in CST so I need to convert the time to CST before storing it to a table.
I am using MSSQL 2008. Any help woul be greatly appreciated.
e.g.
@str_Date = '08/12/2012',
@str_time = '10:30',
@str_AMPM = 'PM'
@str_Tzone = 'EST'
This need to get stored as '08/12/2012 22:30:00.000'
I pretty much got the date and time format part but how to I convert into CST from the EST time?
22:30 is 10:30 PM.
It is somewhat unclear what you are trying to do but depending on your needs you could either look at DATEADD or the datetimeoffset datatype.
http://msdn.microsoft.com/en-us/library/ms186819.aspx
http://msdn.microsoft.com/en-us/library/bb630289.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 28, 2012 at 9:48 am
I am trying to store the EST as CST , PST as CST.
The time value coming from the web application are in their local time zone but ned to convert them to CST to stor in the system.
September 28, 2012 at 9:55 am
Guras (9/28/2012)
I am trying to store the EST as CST , PST as CST.The time value coming from the web application are in their local time zone but ned to convert them to CST to stor in the system.
You want to change the time? So for example if the enter 8pm EST you want to store 7pm?
Like I said DATEADD is probably the easiest thing to use.
DATEADD(h, -1, @DateEntered)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 28, 2012 at 9:58 am
I could but there are certain zones in US that does not adjust the tiem for daylight savings.
September 28, 2012 at 10:04 am
Guras (9/28/2012)
I could but there are certain zones in US that does not adjust the tiem for daylight savings.
Well now there are new rules to your issue. I would not store your data at the local time. Daylight saving is constant issue if you need to know the real time. Instead store all of your data as datetimeoffset. That way you will always know what time it was and you can figure out the offset at the time you insert it. Everything is then based of UTCDate and removes all ambiguity.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 28, 2012 at 11:34 am
Store it in UTC time...what you're asking can end up becoming a huge pain in the you know what.
September 28, 2012 at 11:47 am
I beleive your Web application was developed in .NET and .NET framework has class libraries to acheive this functionality.
Convert it from FRONTEND and send the actual value to your DB.
Further communication on .NET related questions, should not be under this site(as it will violate the rules maintained here) and this just FYI
September 28, 2012 at 12:07 pm
Further communication on .NET related questions, should not be under this site(as it will violate the rules maintained here) and this just FYI
Where are you getting this from? There have been a number of discussions about .NET on this site. They are not typical and for obvious reasons there are not as many responders but I have never seen or heard anything that says we can't discuss .NET.
http://www.sqlservercentral.com/About/Terms
The OP may find better help on a forum dedicated to .NET but there are plenty of people around here who can discuss .NET pretty competently.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply