date and time functions
-
As a beginner in sql im having trouble understanding the date and time functions.Could someone explain why this code returns an error
create table Tem(
DateHired smalldatetime null check (DateHired=getdate()),
TempCol int null
 
insert tem values(getdate(),1)
appreciated
-
Christopher - I think what you want to do is to have the DateHired column DEFAULT to the date that the employee is hired...
change your table def to :
create table Tem(
DateHired smalldatetime default getdate(),
TempCol int null)
A check constraint that you have used is used differently - here's an example from BOL for when you would use a check constraint:
"...it is possible to limit the range of values for a salary column by creating a CHECK constraint that allows only data that ranges from $15,000 through $100,000. This prevents salaries from being entered beyond the normal salary range.."
**ASCII stupid question, get a stupid ANSI !!!** -
Christopher - What Sushila has said is absolutely correct, and I would advise using a default constraint as opposed to a Check constraint in your circumstance.
I may be wrong in my assessment, but I believe the reason you are getting an error returned is because of the amount of time taken to process your insert query results in 2 different date stamps, which causes the check constraint to fire. What I mean is, you execute your insert statement with the GetDate() function, and this returns the current timestamp (e.g. 2005-07-25 12:08:44.437). However, when the query is processed, you Check constraint is kicked off before the data is entered into the table, and this returns it's own timestamp (e.g. 2005-07-25 12:08:44.438), which, as you can see, may be a few ms later, but it is nevertheless a different timestamp than the one recorded for the insert statement.
If nothing else, at least this proves your Check constraint works !!
-
"(e.g. 2005-07-25 12:08:44.437). However, when the query is processed, you Check constraint is kicked off before the data is entered into the table, and this returns it's own timestamp (e.g. 2005-07-25 12:08:44.438)"
Actually it would be 2005-07-25 12:08:44.440 because datetime is precise to only 1/300 of a second. But what really makes this fail is that SMALLDATETIME is precise to the minute. So if you enter "2005-07-25 12:08:44.437" what is really entered is "2005-07-25 12:08:00.000" (or 12:09 don't remember if it's rounded or truncated and I can't test from here). But again Sushila is correct, you actually need a default constraint and not a check constraint.
-
Actually it would be 2005-07-25 12:08:44.440 because datetime is precise to only 1/300 of a second.
How do you know this stuff, Remi ??
-
He's our resident genius - Phil - that's how!
**ASCII stupid question, get a stupid ANSI !!!** -
Check the books online... it's buried somewhere in the datetime datatypes
!!!Yeah yeah, back to my vacations
. -
also in ken henderson's book - & probably a lot of other places...but the point is that you know and retain these facts and many of us don't...
now go on and G.A.L remi...
**ASCII stupid question, get a stupid ANSI !!!** -
I have a life, I'm playing with the nefew while answering this. But come to think of it this is all your fault... I had to browse the forum while I waited for your big discovery. I wouldn't have answered to this question if it were not because of you
. -
Quoi ?!?!
My big discovery was 2 days ago...and how come you always know which forums call out your name remi ?! do you actually get email notification everytime someone says remi on ssc.com - 'cos if that's the case your inbox must be full to overflowing!
go browse tic tac toe...today - your name is being called out everywhere!
**ASCII stupid question, get a stupid ANSI !!!** -
I said I had to browse while waiting your reply to my reply... that's all.
-
BTW 2 refferences on 18 posts is harldy being called everywhere.
Back to life now
. -
She is baiting you to win a bet. How was i to know pie was her favorite.
Mike
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply