Professional Web Applications Themes

Dynamic time zone per instance or database - Microsoft SQL / MS SQL Server

Is there a way to get SQL to report times per database or service instance as a different time zone then the system is setup. I need to host a database for a company in England ( we are in central time US ). We need the times to report as England time. Currently we are just going to use another server and set it up as if it was running in England. Eventually we are going to re-write our software to either use getUTCdate and adjust, or just adjust from central time. It would be cool if SQL server ...

  1. #1

    Default Dynamic time zone per instance or database

    Is there a way to get SQL to report times per database or
    service instance as a different time zone then the system
    is setup.

    I need to host a database for a company in England ( we
    are in central time US ). We need the times to report as
    England time.

    Currently we are just going to use another server and set
    it up as if it was running in England. Eventually we are
    going to re-write our software to either use getUTCdate
    and adjust, or just adjust from central time.

    It would be cool if SQL server or maybe a utility of some
    sort could report the time differently, it would save us
    some costs on the server and time developing. We will re-
    write anyways to handle users in different time zones, but
    short term it would be nice.

    thanks
    twiggy Guest

  2. #2

    Default Re: Dynamic time zone per instance or database

    I'd keep the systems on UTC. You can adjust to lawful time (that means
    local timezone and DST rules) in the displays, but still get the right
    summaries over time.

    Standard SQL has a whole set of schema information tables and
    conventions for doing what you need, but you might have to wait to Yukon
    to get it.

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

  3. #3

    Default Re: Dynamic time zone per instance or database

    Twiggy,

    SQL server stores dates internally as you give it to it. So if the
    dates weren't corrected to UTC originally they will still be in the
    local time of the client that passed them to SQL, or the SQL
    server's time if you used GetDate() / current_timestamp.

    ie When you go

    Update MyTable
    set MyDateCol = '20031225'

    or MyDate or GetDate() or whatever, there is no provision
    for you to enter a timezone, so what you enter is what you get.

    Timezone offsets ( from UTC not GMT - there aren't quite identical ),
    are normally done client side, often implicitly, hiding the region settings,
    daylight savings etc lookup in a nice function such as you will no doubt use.

    If you want to keep a particular database in SQL server on a different
    time, then you could write your own functions for GetDate() and
    current_timestamp, and replace any calls to those with your own
    ( Overloading does not work ). I wouldn't recommend this approach.
    As Joe Celko suggests - when you have to support multiple timezones,
    then using a single standard ( and UTC is THE standard ), and storing
    all times in this offset produces less hassles all round.

    "twiggy" <com> wrote in message news:05c601c366b6$7e02a750$gbl... 

    If you don't have complicated time issues, such as daylight savings, multiple offsets, or new
    data arriving whilst you update, then maybe not.
    Dynamic sql and information_schema.columns, can do some wonderful things.

    Regards
    AJ


    Andrew Guest

  4. Moderated Post

    Default Re: Dynamic time zone per instance or database

    Removed by Administrator
    Joe Guest
    Moderated Post

Similar Threads

  1. Time Zone and Time are incorrect
    By Greg Candido in forum Windows Server
    Replies: 0
    Last Post: June 30th, 10:25 AM
  2. Time Zone
    By gt in forum AIX
    Replies: 2
    Last Post: November 29th, 06:43 PM
  3. Time Zone
    By jet in forum Macromedia Director Lingo
    Replies: 0
    Last Post: October 7th, 08:35 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139