SQL date and time and different time zone

Posted on 2007-08-06
Last Modified: 2009-01-30
I have a .net application using SQL 2000 database. the application and DB is running on a server in US and therefore saving the time and date using US time. Being nearly one day in front of US time, I need to convert the dates to our time zone. In the database table the function getdate() is used for a date column. how can I modify it so I could save the date in the table using our time zone when data are submitted?

Thanks in advance

Question by:shmz
    LVL 51

    Expert Comment

    You only have two choices for the current date.  GETDATE() and GETUTCDATE() which have to be converted into any other zones.  You can use DATEADD()

    DATEADD(day, 1, GETDATE()) will add one day onto the current date.  You can use negative values as well.
    LVL 13

    Accepted Solution

    Since the server is based on US..its good idea to store the values using US time zone. .. your application should there fore convert it to the required time where the user is based.
    keeping a database server in US and then keeping a date field for another time zone is not a good idea. it will create more confusion for the new staff. ( unless u keep two different fields. one for us and one for ur)

    in this case i think getutcdate() will help u as it will store a universal time zone on the database. and all applications will have to cnvert the data that is shown to users to the local time zone.

    LVL 51

    Expert Comment

    Wizilling makes a good point and something I forgot to add.  All systems I design store dates as UTC and then I convert to local time zones.  Even with web applications you can convert the time zone at the client side using JavaScript.  It eliminates a LOT of headaches.

    Sadly I work at a large multi-national firm whose headquarters are in California.  So guess which time zone is the default? (SIGH) Yes PST.  So I often fix many bugs with date math issues due to some process misinterpreting the date time zone because the a developer assumed that the date was in their time zone.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now