Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Getdate() problem

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts
Posted - 2005-10-31 : 07:36:59
Ranjit writes "Hi there,

I'll try to make this short.
My site allows users to update their submitted content. When they update, both the new content and the current date/time should be entered into the MSSQL database.

When a user first inserts data, the "FirstPosted" column [ datatype "smalldatetime", default value "(getdate())" ] does the job of automatically storing the current date and time. No hidden fields are used and the insert statement too does not require the "FirstPosted" field to be used.

The problem occurs when updating. GETDATE() doesn't seem to work. The column "RecentUpdate" has the same datatype and default value as the above "FirstPosted" column.

In the update page, if I use a hidden field for "RecentUpdate" with value =Now() it works (the current date/time is shown), but only when running the site on my local testing server. When I upload the same page to the remote server and use update, an error is thrown up "The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value."
I've tried =Getdate() in the hidden field but it gives a syntax error. I've also tried removing the hidden field and get the error "column doesn't allow NULLS", even though NULL is unchecked in the table.
The problem cannot lie in the date config on the remote server since insert works. Why does the error occur only for update and how do I resolve it?
I use WinXP, SP2, MSSQL Server 2000 and Dreamweaver MX2004.
Any help is appreciated.
Thanks in advance

Ranjit"

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2005-10-31 : 08:20:54
recentupdate can be handled in 2 ways.
1. in a trigger where you update the updated rows
2. in code where you update the recentupdate column. here you must enclose date in single quotes '31.10.2005'
you must also hanlde proper locale for dates. 5/9/2005 can be interpreted in 2 ways... either 5th nov or 9th may


Go with the flow & have fun! Else fight the flow
Go to Top of Page

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts
Posted - 2005-10-31 : 08:29:01
NULL unchecked in the table means you do NOT allow nulls for that column.

In your update stored proc, why not simply insert the current date?


UPDATE myTable
SET 
    Col_1 = @Col_1_Var
    Update_time_colum = getdate()
WHERE
    Index_column = @passed_in_Index




Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)
Go to Top of Page

Ranjit
Starting Member

2 Posts
Posted - 2005-10-31 : 12:22:31
quote:
Originally posted by spirit1

recentupdate can be handled in 2 ways.
2. in code where you update the recentupdate column. here you must enclose date in single quotes '31.10.2005'


Thanks for replying. When a user updates his content, the current system date and time should be used in the update. The user does not manually enter the date. This can only be got from Getdate() or Now().

The question remains unanswered : Why does Now() work on my local testing server but not on the remote server, despite the tables being identical with the same data types and default values?
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2005-10-31 : 12:26:35
well now() is vb function so you must do:
"Update table1 set ..., date = '" & Now() & "' "
do you have same locale on both computers?


Go with the flow & have fun! Else fight the flow
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2005-10-31 : 12:26:42
DonAtWork gave you the answer.

Now() is a VB/VBA function, not a T-SQL function. In what context are you using the Now() function?

When you say it is "not working", what does that mean: The wrong date is stored? An error message? Nothing happens? The PC crashes?
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2005-10-31 : 13:54:35
I would recommend that you do not mix Now() and GetDate() - i.e. have some values allocated by the application and some by the SQL Server (don't forget the Default values in SQL Server).

The two machines are bound to have slightly different times, maybe different approaches to Daylight Saving Time and might even be/move to different time zones! The last is even more applicable if you use the Application Date from Client machines.

Thus, for me, things that need "Now" get given that by SQL Server, not anything else.

Kristen
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2005-10-31 : 13:56:20
well we use now().
because the client's want to know last update date in their time not our server time.


Go with the flow & have fun! Else fight the flow
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2005-10-31 : 14:15:00
So how do you tell what happened in what order in an audit sense?

(I would have thought that converting the SQL Server GetDate() to "local time equivalent" would have been a satisfactory answer for the user, but provided more auditing/comparison abilities centrally)

Kristen
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2005-10-31 : 14:19:31
luckily we don't need auditing. dates when something happened isn't that important

Go with the flow & have fun! Else fight the flow
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2005-10-31 : 14:24:21
"luckily we don't need auditing"

No fraud eh? I'm moving to Slovenia then!

Kristen
Go to Top of Page

rrb
SQLTeam Poet Laureate

1479 Posts
Posted - 2005-10-31 : 21:35:48
Yep, and for what its worth, I reckon you should ALWAYS cast Now() to a string explicitly at the client, and then cast that string back to a datetime value at the server so that there's no confusion if your Australian user has a different default date format. eg
SQL = "Update table1 set ..., date = convert(datetime, '" & Format(Now(),"yyyymmdd hh:nn:ss") & "', 112) "



(PS check the actual convert code in BOL, I'm guessing cause I don't have a copy of BOL in front of me.)


--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2005-11-01 : 01:21:31
"I'm guessing cause I don't have a copy of BOL in front of me.)"

No parameter on CONVERT going from String to Datetime - that applies when going the other way [from Datetime to String]

BoL says: "(Unseparated String Format) Six- or eight-digit strings are always interpreted as ymd. The month and day must always be two digits. The SET DATEFORMAT session setting does not apply to all-numeric date entries (numeric entries without separators)."

Either use "yyyymmdd hh:mm:ss.mmm" or "yyyy-mm-ddThh:mm:ss.mmm" as the string format. Do not use hyphen separators without the "T" [because it works 99% of the time ... but not 100%!!]

From the client you can also provide date & time data in the formats [to ODBC/etc.]:

{ ts '1998-05-02 01:23:56.123' }
{ d '1990-10-02' }
{ t '13:33:41' }

Kristen
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2005-11-01 : 06:22:12
quote:

No fraud eh? I'm moving to Slovenia then!
Kristen


LOL! there is fraud i guess... but we don't deal with finacial or any other kind of data that would need strict auditing.
we do perform daily backups and hourly tran log backups...

Go with the flow & have fun! Else fight the flow
Go to Top of Page

Ranjit
Starting Member

2 Posts
Posted - 2005-11-02 : 08:18:50
Tried some of the methods given above but what I finally settled on was the following simple code. I put this at the top of the update page.

<%
Session.LCID = 2057
%>

The Local ID code 2057, sets the locale settings on the server to United Kingdom English.
Now the results page starts to display the date in the format I want. eg. Wednesday, 2nd November 2005 instead of Friday, 11th February 2005 (ie. 2-11-2005 and not 11-2-2005)

Still had to use a hidden field "RecentUpdate" value <%=Now()%>
Without this hidden field, the current date/time is not updated despite the column's default value being "getdate()" and NULL unchecked. It used to show a blank in the results page.
Hidden field is not required when doing an Insert on current date/time, but still works. (scratching head)

Might be better to put the LCID code in a Global.asa file which would work for all the pages.

Sub Session_OnStart
Session.LCID = 2057
End Sub

Anyway, thanks all.
I'll check here later.
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2005-11-02 : 08:42:22
quote:
Originally posted by rrb

Yep, and for what its worth, I reckon you should ALWAYS cast Now() to a string explicitly at the client, and then cast that string back to a datetime value at the server so that there's no confusion if your Australian user has a different default date format. eg
SQL = "Update table1 set ..., date = convert(datetime, '" & Format(Now(),"yyyymmdd hh:nn:ss") & "', 112) "



(PS check the actual convert code in BOL, I'm guessing cause I don't have a copy of BOL in front of me.)


--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"


No -- you should always use parameters and pass the VALUE of now(). Never pass string representations of values concatenated into sql statements, pass in VALUES using parameters. Better for security, of course, and also for issues like this. Formatting should never be an issue when you put data INTO sql server or take data OUT. It should be only DATA that you are working about.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site