Dateonly as Default value - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Dateonly as Default value

Author

Topic

dbaman
Starting Member

46 Posts

Posted - 2008-04-28 : 09:51:03

Please suggest how I can do this:

I want to get just the date part as the default value in my SQLServer2005 table.

I created the table with datatype as "smalldatetime" and in default value i have "(getdate())". I get Date&Time with this. However I want just the date.

Thanks for your input.

R

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-28 : 09:51:59

use dateadd(day,datediff(day,0,getdate()),0)

Madhivanan

Failing to plan is Planning to fail

dbaman
Starting Member

46 Posts

Posted - 2008-04-28 : 10:02:27

Thanks Madhivanan for the reply.

Even With this
dateadd(day,datediff(day,0,getdate()),0)
I get MM/DD/YYYY 12:00:00AM

R

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-28 : 10:09:50

Of course!
The target column has smalldatetime as datatype. What did you expect?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-28 : 10:11:28

If you expected MM/DD/YYYY only, then you can't use smalldatetime anymore, because MM/DD/YYYY is not a date in sql server 2005.
It is a string of characters which human mind interprets as a date.

You can do this with SQL Server 2008 and still use the new DATE datatype.

E 12°55'05.25"
N 56°04'39.16"

dbaman
Starting Member

46 Posts

Posted - 2008-04-28 : 10:13:52

Thanks Peso for the reply.You are right. I need to find other way to make this. I want just the date part in the column. I might need to pick another datatype then. Any better way to make this to work?

I want the data in column lets say
DOB: 12/20/2005

R

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-28 : 10:23:54

quote:
Originally posted by dbaman

Thanks Peso for the reply.You are right. I need to find other way to make this. I want just the date part in the column. I might need to pick another datatype then. Any better way to make this to work?

I want the data in column lets say
DOB: 12/20/2005

R

Why do you want to omit time part?
Where do you want to show dates?

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-28 : 10:28:26

quote:
Originally posted by dbaman

Thanks Peso for the reply.You are right. I need to find other way to make this. I want just the date part in the column. I might need to pick another datatype then. Any better way to make this to work?

I want the data in column lets say
DOB: 12/20/2005

R

Are you really concerned with storing time values in table? You can still retrieve only the datepart from table for displaying and for other operations.

dbaman
Starting Member

46 Posts

Posted - 2008-04-28 : 10:34:03

Thanks Madhivanan and Peso for all the replies.

I have a table with column name Job-Start-Date . Only date is desired in that column, no time.

R

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-28 : 10:38:59

Keep the column as smalldatetime and worry about formatting at the front end.
Converting the date to a string makes it harder to compare and sort the records.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources