Date time types default to UTC and cannot be changed
Whenever I use the INSERT INTO template in SQL Prompt 5, I get an incorrect date and/or time inserted for all date/time columns detected in the table. The time is 8 hours ahead of my time zone. I am on Pacific Standard Time, thus running the INSERT INTO template at say, 4pm, will generate a default date/time stamp of the next day at midnight (2011-01-06 16:00:00 real time produces a default insertion of 2011-01-07 00:00:00 for all date/time column values in the proposed INSERT statement). My assumption is that the default time is set to UTC which makes sense for Red Gate.
It would be great if SQL Prompt could detect and use the time zone on the server.
Anonymous
shared this idea
Prompt 6.3 uses GETDATE() rather than inserting a string literal with the current date
-
AJ Phillips
commented
I made a comment about this issue in SQL Prompt 5 over a year ago and the issue remains in SQL Prompt 6, at least in preview version 6.0.0.270. The default regional time zone set on the host is the way to go. Honestly, an even better option is to create a client setting that allows an end user to specify it directly, wherein it aligns with the host time zone by default of course. I can imagine some (maybe more than a few) scenarios where frequent travelers might have a need to override their client time zone setting in SQL Prompt (should you create one) while working on batch scripts inserts/updates/merges for a client in another time zone.
-
Brent McCracken
commented
timezone of the server or the timezone of the client doesn't matter to us in New Zealand - there is only 1 timezone :-)