Prev: RSExecutionLog Error: DTSER_FAILURE (1). Options
Next: The database is being closed before database mirroring is fully initialized. The ALTER DATABASE command failed
From: Rob on 9 Feb 2009 11:46
While I can successfully parse the following query...

CREATE TABLE TEMP (
STARTEFFECTIVEDATE DATE,
ENDEFFECTIVEDATE DATE,
)
GO

....it fails with this error when I run it:

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

What am I doing wrong here? I simply need to store the date value without
time.

TIA.
From: Russell Fields on 9 Feb 2009 11:57
Rob,

"Date" is a datatype in SQL Server 2008, but not the earlier versions.
Before that, datetime is the standard choice for tracking dates. (Or
smalldatetime.)

If you are using SQL Server 2008, make sure that the database is running in
SQL Server 2008 (10) compatibility, and not in (90) or earlier. The earlier
compatibility will disable new 2008 keywords.

RLF

"Rob" <Rob(a)discussions.microsoft.com> wrote in message
news:5D621553-699D-4EC2-9ED7-975DFFC4459A(a)microsoft.com...
> While I can successfully parse the following query...
>
> CREATE TABLE TEMP (
> STARTEFFECTIVEDATE DATE,
> ENDEFFECTIVEDATE DATE,
> )
> GO
>
> ...it fails with this error when I run it:
>
> Msg 2715, Level 16, State 7, Line 1
> Column, parameter, or variable #1: Cannot find data type DATE.
>
> What am I doing wrong here? I simply need to store the date value without
> time.
>
> TIA.

From: Vern Rabe on 9 Feb 2009 12:01
You didn't mention what version of SQL Server you're using, but it must be
pre-2008, because the DATE data type doesn't exist until then. You'll need to
use either DATETIME or SMALLDATETIME. Both of these contain time information,
but if not explicitly included in INSERT statements, it defaults to 12:00:00
(midnight).

Vern Rabe

"Rob" wrote:

> While I can successfully parse the following query...
>
> CREATE TABLE TEMP (
> STARTEFFECTIVEDATE DATE,
> ENDEFFECTIVEDATE DATE,
> )
> GO
>
> ...it fails with this error when I run it:
>
> Msg 2715, Level 16, State 7, Line 1
> Column, parameter, or variable #1: Cannot find data type DATE.
>
> What am I doing wrong here? I simply need to store the date value without
> time.
>
> TIA.
From: Plamen Ratchev on 9 Feb 2009 12:02
The DATE data type was introduced in SQL Server 2008 and you cannot use
it on prior SQL Server versions. If not on SQL Server 2008 you would
have to use the DATETIME data type (or SMALLDATETIME). Then you can
create a check constraint to enforce that time is always set at midnight:

datecol DATETIME NOT NULL
CONSTRAINT df_datecol
DEFAULT DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0),
CONSTRAINT ck_datecol
CHECK (datecol = DATEADD(day, DATEDIFF(day, 0, datecol), 0))

--
Plamen Ratchev
http://www.SQLStudio.com
From: Rob on 9 Feb 2009 15:09
Thanks Russel, Ver, and Plamen.

I should have been more explicit about the environement I was working in. It
is indeed in SQL 2005, where the Date data type is not allowed. Silly me.

Thanks again.

"Rob" wrote:

> While I can successfully parse the following query...
>
> CREATE TABLE TEMP (
> STARTEFFECTIVEDATE DATE,
> ENDEFFECTIVEDATE DATE,
> )
> GO
>
> ...it fails with this error when I run it:
>
> Msg 2715, Level 16, State 7, Line 1
> Column, parameter, or variable #1: Cannot find data type DATE.
>
> What am I doing wrong here? I simply need to store the date value without
> time.
>
> TIA.
 | 
Pages: 1
Prev: RSExecutionLog Error: DTSER_FAILURE (1). Options
Next: The database is being closed before database mirroring is fully initialized. The ALTER DATABASE command failed