locked
GetDate() to auto fill in with a targeted timezone RRS feed

  • Question

  • User-1633045945 posted

    Hello,

    I have a server that is in Pacific Time and my current customers are pacific time users and when records are inserted into the database, the pacific time is automatically recorded.

    My question is, if I create a new database for a central or eastern timezone on my west coast server, how do I set the time zone for that database to reflect a central or eastern timezone?  I just really need the getdate() function to reflect the right timezone date and time for that specific database.

    I hope that question makes sense.

    Friday, October 5, 2018 5:14 PM

Answers

  • User475983607 posted

    So if I have some databases on my machine that is in the pacific time zone and my users are in another time zone, then there isn't a way to set that specific database's timezone to that user's timezone.

    The user's machine knows the timezone setting which means the browser is aware of the timezone.  JavaScript date type knows how to read an ISO formatted date and convert the date to the machine's time zone.  However, browser does not send the user's timezone in an HTTP request to the server.  If you want to handle multiple time zones you need to design this feature and write a bit of code.  The common method is storing timezone sensitive dates as UTC and letting the user select their timezone setting in the application. 

    It is possible to detect the timezone offset using JavaScript and send that to the server.  

    https://stackoverflow.com/questions/13/determine-a-users-timezone

    What function could I use instead of GETDATE() that will return the current date and time of a specific time zone?

    GETDATE() runs in SQL server. The user is either coming from the connection string or the application pool identity.  But GETDATE() is looking at the machine's system clock as stated in the first line of the reference documentation.

    Returns the current database system timestamp as a datetime value without the database time zone offset. This value is derived from the operating system of the computer on which the instance of SQL Server is running.

    https://docs.microsoft.com/en-us/sql/t-sql/functions/getdate-transact-sql?view=sql-server-2017

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, October 5, 2018 7:49 PM
  • User77042963 posted

    Okay, so is there another function other than GETDATE() within sql server, that I can use in the Default VAlue setting of a DateTime field that I can pass a timezone parameter to that will return the date and time of that timezone that I passed to it... within SQL Server? 

    I don't want to script it from a page.

    GETUTCDATE()

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 8, 2018 3:14 PM

All replies

  • User475983607 posted

    The timezone is set in the OS.  GetDate() will reflect the OS setting.  You can test this yourself by updating your development machine's Timezone and invoking GETDATE() in SSMS.

    If you need to handle multiple time zones, generally you'll want to store dates as UTC and let the user configure their timezone setting.

    Friday, October 5, 2018 6:37 PM
  • User-1633045945 posted

    So if I have some databases on my machine that is in the pacific time zone and my users are in another time zone, then there isn't a way to set that specific database's timezone to that user's timezone.

    What function could I use instead of GETDATE() that will return the current date and time of a specific time zone?

    Friday, October 5, 2018 7:10 PM
  • User475983607 posted

    So if I have some databases on my machine that is in the pacific time zone and my users are in another time zone, then there isn't a way to set that specific database's timezone to that user's timezone.

    The user's machine knows the timezone setting which means the browser is aware of the timezone.  JavaScript date type knows how to read an ISO formatted date and convert the date to the machine's time zone.  However, browser does not send the user's timezone in an HTTP request to the server.  If you want to handle multiple time zones you need to design this feature and write a bit of code.  The common method is storing timezone sensitive dates as UTC and letting the user select their timezone setting in the application. 

    It is possible to detect the timezone offset using JavaScript and send that to the server.  

    https://stackoverflow.com/questions/13/determine-a-users-timezone

    What function could I use instead of GETDATE() that will return the current date and time of a specific time zone?

    GETDATE() runs in SQL server. The user is either coming from the connection string or the application pool identity.  But GETDATE() is looking at the machine's system clock as stated in the first line of the reference documentation.

    Returns the current database system timestamp as a datetime value without the database time zone offset. This value is derived from the operating system of the computer on which the instance of SQL Server is running.

    https://docs.microsoft.com/en-us/sql/t-sql/functions/getdate-transact-sql?view=sql-server-2017

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, October 5, 2018 7:49 PM
  • User-1633045945 posted

    Okay, so is there another function other than GETDATE() within sql server, that I can use in the Default VAlue setting of a DateTime field that I can pass a timezone parameter to that will return the date and time of that timezone that I passed to it... within SQL Server? 

    I don't want to script it from a page.

    Friday, October 5, 2018 9:48 PM
  • User475983607 posted

    Okay, so is there another function other than GETDATE() within sql server, that I can use in the Default VAlue setting of a DateTime field that I can pass a timezone parameter to that will return the date and time of that timezone that I passed to it... within SQL Server? 

    I don't want to script it from a page.

    Convert the DateTime to UTC.   Then all the times are from the same timezone.

    Friday, October 5, 2018 10:52 PM
  • User1724605321 posted

    Hi mstrsftwr,

    GETDATE function returns the current database system timestamp as a datetime value without the database time zone offset. This value is derived from the operating system of the computer on which the instance of SQL Server is running.

    To handle multi-timezone in SQL Server , you can try keep UTC date and use DATETIMEOFFSET data type to store a local date and time value, including its time zone offset from UTC. T-SQL provides supporting functions called TODATETIMEOFFSET and SWITCHOFFSET to enable you to convert a nonoffset aware value plus an offset to this type and to switch the offset of an offset aware value to a target offset, respectively. 

    From Sql Server 2016 , you can look into the new AT TIME ZONE to converts date to a specified time zone considering DST (daylight saving time) changes:

    https://docs.microsoft.com/en-us/sql/t-sql/queries/at-time-zone-transact-sql?view=sql-server-2017 

    https://www.itprotoday.com/microsoft-sql-server/what-you-need-know-about-datetimeoffset-and-sql-server-2016s-time-zone-function 

    Best Regards,

    Nan Yu

    Monday, October 8, 2018 3:01 AM
  • User77042963 posted

    Okay, so is there another function other than GETDATE() within sql server, that I can use in the Default VAlue setting of a DateTime field that I can pass a timezone parameter to that will return the date and time of that timezone that I passed to it... within SQL Server? 

    I don't want to script it from a page.

    GETUTCDATE()

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 8, 2018 3:14 PM
  • User-2082239438 posted

    mstrsftwr

    Okay, so is there another function other than GETDATE() within sql server, that I can use in the Default VAlue setting of a DateTime field that I can pass a timezone parameter to that will return the date and time of that timezone that I passed to it... within SQL Server? 

    I don't want to script it from a page.

    GETUTCDATE()

    There is two function available for Date time.

    SELECT GETDATE() AS ReturntheOSDateTime, ---OS Date based on the configured timezone
    		GETUTCDATE() AS ReturntheUTCDate ----UTC Date
    

    You will get more details on this Link : GETUTCDATE

    Note that you will need to modify your code to meet your requirement too.

    Saturday, October 20, 2018 12:49 PM