Error when altering to date datatype.

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

General SQL Server Forums

New to SQL Server Programming

Error when altering to date datatype.

Author

Topic

rowter
Yak Posting Veteran

76 Posts

Posted - 2010-01-07 : 11:42:15

Hi,

I am using sqlserver 2005 express edition.
I have couple of fileds like Startdate, end date where time is not required. They were declared as datetime datatypes.
I need to change the datatype of these column from datetime to date so that only date is displayed.

I used the following :

ALTER TABLE SCHULED_DATE Alter column Start_Dt date

I get the following error:
Column, parameter, or variable #62: Cannot find data type date.

Ths following link shos that there is a date datatype.
http://msdn.microsoft.com/en-us/library/bb630352.aspx

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 11:43:53

are you using sql 2008? date datatype is available only after sql 2008 with compatibility level 100

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-07 : 11:53:26

Your posted link:

quote:
SQL Server 2008 Books Online (November 2009)
date (Transact-SQL)

Your posted version:

quote:
I am using sqlserver 2005 express edition.

No, you're never too old to Yak'n'Roll if you're too young to die.

rowter
Yak Posting Veteran

76 Posts

Posted - 2010-01-07 : 12:13:47

Hi,

So, for 2005 there is no date datatype?
Is there anyway i can retrieve only the date from the column and ignore the time cmponent?

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 12:19:03

quote:
Originally posted by rowter

Hi,

So, for 2005 there is no date datatype?
Is there anyway i can retrieve only the date from the column and ignore the time cmponent?

Thanks.

yup you can


SELECT DATEADD(dd,DATEDIFF(dd,0,yourdatecolumn),0) FROM table

will strip off timepart

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-07 : 13:49:54

For display purposes, you can make use of the CONVERT options to get your desired format..

select convert(varchar(10),getdate(),120)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 13:57:21

quote:
Originally posted by vijayisonly

For display purposes, you can make use of the CONVERT options to get your desired format..
select convert(varchar(10),getdate(),120)

I prefer to do it at front end application using formatting functions available there. this can cause problem particular if you need to do date manipulations in query later using this field since its been converted to varchar now.

rowter
Yak Posting Veteran

76 Posts

Posted - 2010-01-07 : 14:48:16

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-08 : 02:07:58

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources