Pkey Date default value

Question

Post reply

Pkey Date default value

krypto69

SSChampion

Points: 13604
More actions
October 2, 2009 at 10:06 am

#136252

I have a table I'm inserting records in, it has a pkey date field.
I want to set it so I don't have to pass anything...I think I need to set it's default value
getdate()
But
I was hoping to use yesterdays date (not the current date)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Look up DATEADD in SQL's Books Online.

If you want to default to yesterday at midnight, then check this blog post out.

http://sqlinthewild.co.za/index.php/2007/11/05/datetime-manipulation/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Yesterday at midnight:

select dateadd(dd, datediff(dd, 0, getdate()) - 1, 0)

krypto69 SSChampion Points: 13604 More actions · Answer 3

Lynn

if I add that in the default value or binding..it doesn't auto fill it for me when I insert a record into that table

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

I'd have to see the code for the insert as well as the DDL for the table. I have an idea, but it is better to see this first.

krypto69 SSChampion Points: 13604 More actions · Answer 5

shouldn't I be able to simply add 'GETDATE()' to the default value or binding of the table, so it inserts the current date when a record gets inserted?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

krypto69 (10/2/2009)
shouldn't I be able to simply add 'GETDATE()' to the default value or binding of the table, so it inserts the current date when a record gets inserted?

Show me the insert statement you are using.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Sample code.

create table dbo.MyTestTable (

MyTestTableID int identity(1,1),

MyDate datetime default(dateadd(dd,datediff(dd,0,getdate()) - 1, 0)),

ADataValue int

)

;

insert into dbo.MyTestTable(ADataValue)

select 1

;

select

MyTestTableID,

MyDate,

ADataValue

from

dbo.MyTestTable;