Set default column to only date.

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)

Set default column to only date.

Author

Topic

mushy
Starting Member

12 Posts

Posted - 2010-02-19 : 12:50:24

how can i set default column value to only date.

can anybody provide me the syntax.

Thanks gurus.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-19 : 12:51:46

only what date?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-02-19 : 13:14:38

Set the column's datatype to DATETIME. That will only allow valid dates (or Date + Time) to be stored

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-19 : 13:21:23

or did you mean something like this?


DECLARE @test table
(ID int identity(1,1),
Val varchar(10),
dateval datetime default dateadd(dd,datediff(dd,0,getdate()),0)
)

INSERT @test (Val)
VALUES ('Test'),
('Test1')

SELECT * FROm @test

output
-------------------------------
ID	Val	dateval
1	Test	2010-02-19 00:00:00.000
2	Test1	2010-02-19 00:00:00.000

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mushy
Starting Member

12 Posts

Posted - 2010-02-19 : 14:18:18

Sorry for confusion,

I want to enter inserted date into a column when a row gets inserted in the table.

but when i use "default(getdate())" both date and time get inserted in the table, i am trying to insert only date in a column, every time a row gets inserted.

thanks guys

quote:
Originally posted by visakh16

only what date?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-19 : 14:23:16

quote:
Originally posted by mushy

Sorry for confusion,

I want to enter inserted date into a column when a row gets inserted in the table.

but when i use "default(getdate())" both date and time get inserted in the table, i am trying to insert only date in a column, every time a row gets inserted.

thanks guys

quote:
Originally posted by visakh16

only what date?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

then look at my last suggestion. its what you want.


...
dateval datetime default dateadd(dd,datediff(dd,0,getdate()),0)
....

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-02-19 : 19:38:03

A datetime datatype will always contain both a date and time specification. The best you can do is reset the time portion to the start of the day (e.g., Midnight). The code example which Visahkm has generously supplied will do this for you. If you really want only the Date then you need to upgrade to SQL 2008 which has both DateTime and Date data types.

=======================================
Few things are harder to put up with than the annoyance of a good example. (Mark Twain)

mushy
Starting Member

12 Posts

Posted - 2010-02-21 : 19:37:47

Thanks guys , this will work,

Visakh thanks for the code.

bye

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-22 : 08:09:55