Post reply

DateTime Conversion Between Time Zones

  • November 3, 2003 at 3:59 am

    #160342

    My Problem is

    I have a Table Which Strores TimeStamp From system date using Getdate() Function

    SQL Server is Running on IST Time and Sometimes i need to extract the Reports or data From the Query in Diffrent Time Zone Like EST,CT etc.

    I Created one component for Converting TimeZone From one to other in VB and Created one wrapper Stored Procedured For the Component

    Now i Got Two Ways to Get the data according to datetime stamp

    1.

    Select * From DataTable Where ConverDate(DateTime,IST,CT) between "10/23/2003 00:00:00" and "10/23/2003 00:00:00"

    2.

    Select * From DataTable Where DateTime between ConverTDate("10/23/2003 00:00:00",IST,CT) and ConverTDate("10/23/2003 00:00:00",IST,CT)

    Now the Problem which i am facing is that if i use 1st method to Fetch the records than this take very much time because for every row one component is bieng created and if more that one timestamp field has to be converted in one row than the performance goes poorer

    In Second case the datetime stamp data shown in the report is actaully ist timestamp but between CT TimeZone time Period

    That is data is correct but misinterprated

    If anybody is having a fast and easy way to solve this problem than i will be very greatfule to him/her


    Rohit

  • November 3, 2003 at 4:18 am

    #480609

    http://www.devx.com/tips/Tip/17668




    My Blog: http://dineshasanka.spaces.live.com/

  • November 3, 2003 at 4:41 am

    #480612

    quote:

    http://www.devx.com/tips/Tip/17668

    Unfortunately as US times are now different the sample you provide will come up wrong. For example Eastern was 4 hours behind so your eample was Oct 12, 2003 which before the last Sunday in Oct would have slid 4 hours now using current times it will slide 5.

    Take a look at this thread http://www.sqlservercentral.com/forum/topic.asp?TOPIC_ID=17737&FORUM_ID=8&CAT_ID=1&Topic_Title=UTC+Dates&Forum_Title=T%2DSQL and you will see several comments on this and the method I use to switch Eastern to GMT and vice-versa. You can apply the table method suggested to support the value difference between time zones ad work out you UDF from that. You run into the same issue in all programming languages with regards to dates before and after the time change. As 2AM to 2 AM in fall causes issues so it is suggested to store your dates at the time of entry with UTC date.

  • November 7, 2003 at 8:08 pm

    #481345

    Hi to all,

    Thanks for your response

    but the problem still persist

    i need to know that is their any way to convert from one time zone to anoyther without hampering the performance

    The component i created is giving fine results with daylight saving changes

    but when i call the component wrapper function in query giving more than 1000 records than it hampers performance

    cause for each row's field the component is called created


    Rohit

  • November 9, 2003 at 11:07 pm

    #481374

    see the convert() in BOL.

    Venkata Srinivasa Rao


    Venkata Srinivasa Rao

  • November 12, 2003 at 8:53 pm

    #481792

    Hi Venkata,

    can u be give me details


    Rohit

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply