May 12, 2009 at 7:19 am
Hi I have a very confusing issue i am hoping someone will be able to shed some pearls of wisdom on, I have a 3rd party App running asp that inserts activitys and thus rows to a table in sql 2005, this table has such fields as completion date etc. when inserting a new activity it inserts the date and time of the acitivty as actinoed at the web browser. and when viewed in the sql table is correct. however i am trying to develop a back end script that runs to insert activity via sql scripts and SP's including the current date andtime of the activity.
if i use "sql insert getdate()" commands the current date and time is inserted correclty in the table, but then when viewed in the web asp page by the browser app, is viewed and displayed as one hour ahead of the actual time. i have tried using a "convert" script to match what is currenlty in the db of "convert(datetime,getdate(),121" but this whilst inserting the correct time in the query view again shows 1 hr out on the web view. datetiem in the db is viewed as 2009-05-12 13:16:00.000 can anyone help please?? thanks. 🙂
May 12, 2009 at 7:30 am
Can you get your hands on the query that is run from the webpage? You might be able to do that using Profiler.
I'm wondering if the issue might be related to Standard time vs daylight saving time.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
May 12, 2009 at 7:37 am
HI yes i have i think got the query that is raised from the web page, it calls a stored procedure (incidentactivityinsert) that inserts the values defined, as variables. one of which is the "@completiondate" , which is defined previsouly in the SP as "@completiondate datetime," then it passes the insert command with destination columns, then defines values as the variables i presume produced by the activity on the web page, in this isntance "@completiondate" thats all i can find at this stage. is there anything specific i can do to compensate for daylights savings issues presented by the web page and does anyone know why when viewed in the sql table the values are the same??? many thanks its driving me bonkers!!! 🙂
May 12, 2009 at 7:49 am
Can you post the stored procedure?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
May 12, 2009 at 7:58 am
this is the stored procedure i think it is called either by the asp page in the web server or by a java widget in the web server. thanks agian 🙂
May 12, 2009 at 3:06 pm
Unfortunately that doesn't help much. Looks like the change may be happening in the logic that builds the html code.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
May 12, 2009 at 3:14 pm
This sounds like either DST or TimeZone adjustments by the asp code.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 13, 2009 at 2:50 am
Thanks for all the replies, looks like im snookered then! still dont understand why if the asp code is affecting the data, when it writes to the db and i write to the db the value is the same in the db, or appears to be, but when viewed mine is one hr out, im assuming there must be a specific type or datatime set that is used by the asp code, and i need to replicate it in the db insert command, does anyone know of a way to find out the structre of the datetime and its parameters as inserted by the asp code.?
many thnaks 🙂
May 13, 2009 at 7:23 am
A datetime datatype has no internal format or representation distinctions AFAIK, they're all the same. More likely, there is another column that it is using to keep track of this offset. Or possibly an element or attribute in an XML column.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 13, 2009 at 7:44 am
HI I would agree, but i have been doing some investigations and if i reference an already populated field (by the web page previoulsy) as the input to the datetime field with a new entry (ie duplicate a field) it is correctly represented in the web view and the DB, confused or what...! i will have a look for a time offset somewhere but still dont understand how the asp page can use it for one cell but not another when manually or scripted insert.
thanks.:-(
May 13, 2009 at 7:47 am
Have you verified that the database server and web server have the same time zone settings, and that the system clocks on both servers are set correctly?
May 13, 2009 at 7:49 am
kevin.kinsella (5/13/2009)
HI I would agree, but i have been doing some investigations and if i reference an already populated field (by the web page previoulsy) as the input to the datetime field with a new entry (ie duplicate a field) it is correctly represented in the web view and the DB, confused or what...! i will have a look for a time offset somewhere but still dont understand how the asp page can use it for one cell but not another when manually or scripted insert.thanks.:-(
Then the web code is probably adding the 1 hour when it saves and subtracting it when it displays. Is it in the same timezone? I would suggest explicitly checking the windows system date & times settings on both your sql server and the web server, this alone could cause such behavior.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 13, 2009 at 7:49 am
You might want to try and capture the writes using Profiler. You nay find that it's doinf something else at the same time.
Just my 3 cents worth
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
May 13, 2009 at 8:42 am
Thanks All, i have confirmed that the IIS server, the SQL server and the web view client pc are all in the same time zone and have the same time settings on them, also the applicaion has an internal time zone setting and this is set to the same as the servers.
thanks
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply