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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-01-18 : 08:58:45
|
| moe writes "Could anybody can tell me about having a current date automatically on my table I created by using enterprise manager. The field name I put it as date and the data type is datetime and the length is 8. So what I have to do to get the current date on date field automatically without showing time. I appreciate your help. Thanks!" |
|
|
cshah1
Constraint Violating Yak Guru
347 Posts |
Posted - 2006-01-18 : 09:03:45
|
| USE DEFAULTwhen you open your table designer in EM, at the bottom you will see DEFAULT value set it to getdate() |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-19 : 01:47:55
|
| "So what I have to do to get the current date on date field automatically without showing time"SQL Server always stored the time in an object with a datetime datatype.Two ways around this. 1) Either you store the date and time when the record is created, and then when you display the created date you "shop off" the time. To find records updated yesterday you will have to useWHERE MyCreateDate >= '20060118' AND MyCreateDate < '20060119'2) You truncate the time when you create the record so that the create date is "midnight" - then you can just sayWHERE MyCreateDate = '20060118'However, SQL Server will still display the time ("00:00:00.000") so you will still have to chop that off when you display it.Of the two I would prefer (1) because you can ORDER BY MyCreateDate to get a list of the records strictly in the order they were created, rather than (2) which would be by the day they were created only.I've assumed your question about storing the create date of your record. If instead (or in addition!!) you want to store the update date on your record then you need to use a Trigger - if that's the case please ask again.Edit: Inserted missing digit in datesKristen |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-01-19 : 01:57:07
|
Kristen,7 Digits Date ? ?  WHERE MyCreateDate >= '20060118' AND MyCreateDate < '20060119' -----------------'KH' |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-19 : 03:53:15
|
"7 Digits Date"Yeah, "Kristen's time" ... Useless isn't it Kristen |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-01-19 : 06:44:34
|
>>Yeah, "Kristen's time" ... Useless isn't itYes if you suppress duplicates if digit>0  MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
current date on sql server table