Incorrect Date/Time Specified in Insert Template

kooln7

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.

Has anyone seen this anomaly? I have checked my regional settings just in case (Win7 x64) and all is perfect. I use SQL Prompt 5.0.0.651 under SQL Server 2008 R2. I can't find any setting that might control this behavior. Hopefully, I've just missed something. Thanks for the help!

Luke Jefferson

Hi,

Thanks for reporting this issue.

I've just checked and SQL Prompt always uses UTC as the default, like you predicted. Ideally, we'd detect the correct time zone or let you specify it in the options. This is the first time I've seen this reported as an issue. How much of a problem is this for you?

Regards,

Luke Jefferson
Product Manager
Red Gate Software
E: luke.jefferson@red-gate.com

kooln7

Hello Luke. Thanks for the prompt reply (no pun intended)! I must admit I've used SQL Prompt for a long while and this is the first I've seen it. I am able to reproduce the issue on other machines, but I had to at least see if it was just occurring on my regular workstation. I am hoping that I am the only one seeing the issue as I couldn't find it in the forums.

As to your question of its importance to me, I'd like to see an update for a fix if the developers can discover how locale detection might fail if that's the case or an update for an option to counteract the default behavior. It might be a real problem for those relying on exacting precision for their date/time columns, especially if their data sets are large and they don't catch it early.

In my case, the issue doesn't affect me quite as much. I certainly demand precision in my data sets when I can control the outcome, but I usually change the static suggestion for the date/time output of the template anyway. I always change it to GETDATE() as I may take quite some time to revise the statement, especially when integrating the INSERT statement into a larger procedure. I would actually love to see an option to specify a function such as GETDATE() be written to the statement, rather than writing the actual output of GETDATE() directly. I am making an assumption that the code base is doing just that. That would save me the same ton of time that SQL Prompt already brings to my table!

Thanks for your solicitation.