Link to home
Start Free TrialLog in
Avatar of Yogesh_Agarwal
Yogesh_Agarwal

asked on

Date time Variable in SQl SErver 2008

hi,

I want to creat a date variable but whenever i create a date variable i am getting a error...

Msg 2715, Level 16, State 7, Line 1
Column, parameter, or variable #5: Cannot find data type DATE.

I have attached the code below..
create table tbl 
(
sl int Primary key Identity,
Name Varchar(66),
Age int,
Address_ varchar(30),
Dates  DATE
)

Open in new window

Avatar of Aneesh
Aneesh
Flag of Canada image

DATE is available on sql server 2008, make sure that you are running it on sql 2008 for prior  versions you can use datetime
Avatar of Yogesh_Agarwal
Yogesh_Agarwal

ASKER

but i am getting error..
You must be trying to create it on a SQL Server 2005 instance.  Use DATETIME instead of DATE like below.
create table tbl 
(
sl int Primary key Identity,
Name Varchar(66),
Age int,
Address_ varchar(30),
Dates  DATETIME
)

Open in new window

make triple sure the instance of SQL Server you're running is 2008 and if you execute this remote via ODBC or OLEDB you're also using an SQL 2008 driver.

Bye, Olaf.
i am testing thsi codes in SQL Server Management Studio..
>>i am testing thsi codes in SQL Server Management Studio..<<
Correct. But you are using SQL Server 2005 or you compatibility level is set to 90.
i have installed SQL Server 2008.. not sure with its compatibility..
This will show you the version and Service Pack:

Select SERVERPROPERTY('ProductVersion'), SERVERPROPERTY('ProductLevel')

And this will Print in the messages tab the Compatibility Level.

EXEC sp_dbcmptlevel YourDatabaseNameGoesHere

SOLUTION
Avatar of Olaf Doschke
Olaf Doschke
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
yeah i found that i am running sql 2005.. there is no option for sql 2008.. i am sure i have installed sql server 2008... i don know wats going on.. please tell me how to proceed..
As I posted above, you need to change the datatype from DATE to DATETIME as in this snippet.
create table tbl 
(
sl int Primary key Identity,
Name Varchar(66),
Age int,
Address_ varchar(30),
Dates  DATETIME
)

Open in new window

is it possible to correct my sql version?
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
If you don't need the SQL2005 Server anymore then deinstall it. Like CGLuttrell said your SQL2008 Instance must have an instance name. You might list all SQL Server Instances via this:

http://technet.microsoft.com/en-us/library/ms210334(SQL.90).aspx

Bye, Olaf.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
oh man.. actually there was two instances.. mysysname and mysysname/sqlexpress default was sql express.. now after ur suggestion i checked logins and found all to be alright.. thanks for ur help.
great guides..