0

This is a trivial question, I added a field

Alter table MyTbl add datecreated smalldatetime default CONVERT(varchar(10),GetDate(),101)

When I go check the new field, it shows not only the date but also time. I check the table definition (right click on table -> Modify in SSMS) and it does say the default value as GETDATE() [it discarded the convert(...) by default]. Why is this so. Is there a way to give a default date only to datetime field and not the time.

I ended up leaving the time field as it is because it is better to see what is going on in the table esp in initial test phase.

TheTechGuy
  • 16,560
  • 16
  • 115
  • 136
  • 7
    I would still capture the time myself, and use formatting to only provide the date. – OMG Ponies Oct 25 '11 at 14:16
  • +1 @OMG - it's easy to ignore data you don't need but impossible to get it back once you discard it. – JNK Oct 25 '11 at 14:18
  • youve choosen the incorrect answer.t zeroing out with string manipulation is worse . my answer is more correct dealing with datetime operations merely. – Royi Namir Oct 25 '11 at 14:41

3 Answers3

1

this will reset the time section to zero

DateAdd(day, DateDiff(day, 0, PayDate), 0)
Royi Namir
  • 144,742
  • 138
  • 468
  • 792
  • The OP already has zero'd out time. When converted to `varchar` it loses the time portion of the `datetime`. This should give the same results as the OP already has. –  Oct 25 '11 at 14:18
  • I was the upvote on this. Yes, the OP zerod out the time, but zeroing out with string manipulation is worse than this method. ( http://stackoverflow.com/questions/1177449/best-approach-to-remove-time-part-of-datetime-in-sql-server ) – Jamie F Oct 25 '11 at 14:32
  • @gbn , Are you familiar with certificates and symmetric keys permission in sql server ? – Royi Namir Oct 25 '11 at 14:38
  • I am not sure how i will this code. But I decided to keep the time info for debugging purposes. – TheTechGuy Oct 25 '11 at 14:57
1

Just because you are converting the datetime returned from getdate() to a varchar(10), there is another implicit cast from varchar(10) to datetime, as it is the datatype for your field.

You need to have the datecreated field datatype a varchar(10).

Your alter table statement should be:

Alter table MyTbl add datecreated varchar(10) default CONVERT(varchar(10),GetDate(),101)
1

I just tried your ALTER statement, and it works as you expect; when I enter a row without including datecreated, it sets the value to the date with a time of 00:00:00.

The fact that you checked the table definition and it says the default is GETDATE() leads me to believe the column already existed and your ALTER failed.

Jeff Ogata
  • 56,645
  • 19
  • 114
  • 127
  • I just ran a test myself, and `INSERT` ... `SELECT CONVERT(varchar(10),GetDate(),101)` to a Datetime field sets the time value to midnight. – Jamie F Oct 25 '11 at 14:28
  • yes verified with a new datecreated column. It does shows time 00:00:00. Don't know what was wrong. It does show only GetDATE() in default value though. – TheTechGuy Oct 25 '11 at 14:31