July 28, 2008 at 11:34 am
I have a table that needs to have one row per date.
I set the column containing the date ( datetime ) as the primary key.
Are there going to be problems setting the date column as the primary key since time is also included ?
or this a normal practice ?
Thanks
July 28, 2008 at 12:28 pm
If you don't want time data in there, make sure to put a check constraint on the column that forces the hours, minutes, seconds and milliseconds to be zero. After that, it should work.
alter table dbo.Table1
add constraint Table1_NoTime check (datepart(hour, DateColumn) = 0
and datepart(minute, DateColumn) = 0
and datepart(second, DateColumn) = 0
and datepart(millisecond, DateColumn) = 0)
Something like that, but with the real table and column name.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 28, 2008 at 12:47 pm
This check constraint should work also:
alter table dbo.MyTable
add constraint CHK__MyTable__MyDate_time_is_midnight
check ( MyDate = dateadd(dd,datediff(dd,0,MyDate),0) )
July 28, 2008 at 2:13 pm
Either will work. I use my version, because someone who doesn't know the tricks of dateadd won't have the faintest clue what that one does. Could be documented in an extended property, if someone thinks to look there. The datepart version is pretty English-familiar self-documenting.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 28, 2008 at 5:56 pm
Thank You.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply