SQL Date

[AngelicOne]

I tried setting the value type as DATE but it's not being recognize.. I'm using SQL Server 2005.. I though date is a value type but it's not working..

[Lusiphur]

If anything you'd be needing a "DateTime" format as opposed to "date".

Without seeing specific code segments though I wouldn't be able to answer more directly to your issue.

Annnnd.... post # 666 for Lusiphur :icon_twisted:

[AngelicOne]

create table tbl (orderdate date)

simple as that, though the date is not being recognize.

[Lusiphur]

As I said... try DateTime as your dataType :)

[AngelicOne]

Then you're saying that there's no such thing as DATE only?

[Lusiphur]

Check here for a list of data types supported by transact-SQL in SQL Server 2005.

date is supported but I always use dateTime myself *shrug* maybe that link'll help sort your issue.

[Geekitygeek]

Are you running the query in SQL Server 2005 or somewhere in your C# code?
I ran your code in a query on sql server 2008 and it created the table fine. Your syntax isnt at fault; does the table already exist? are you correctly connected to the sql server instance to run the query?

@Lusipher: DateTime is the most commonly used, but SQL supports severeal time/date datatypes:

time
hh:mm:ss[.nnnnnnn]
00:00:00.0000000 through 23:59:59.9999999

date
YYYY-MM-DD
0001-01-01 through 9999-12-31

smalldatetime
YYYY-MM-DD hh:mm:ss
1900-01-01 through 2079-06-06

datetime
YYYY-MM-DD hh:mm:ss[.nnn]
1753-01-01 through 9999-12-31

datetime2
YYYY-MM-DD hh:mm:ss[.nnnnnnn]
0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999

datetimeoffset
YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm
0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 (in UTC)

EDIT: Nevermind, overlooked the last couple of posts lol :p

[AngelicOne]

yes i am connected. All is fine because I already made some tables but when I create a column which has date data type, the date data type is not turning into a blue color, and I believe it means that it's not being recognize as a sql data type

[nick.crane]

I just tried to run the command in MS SQL Server Management Studio Express (2005).
create table tbl (orderdate date)
It does not execute giving the Error:
Msg 2715, Level 16, State 7, Line 1
Column, parameter, or variable #1: Cannot find data type date.

Also, when I use the table designer the only date related types are DateTime and SmallDateTime.
Is this an SQL Express issue or a 2005 v. 2008 issue?

[nick.crane]

It's a 2005 v. 2008 issue.

SQL Server 2008 New DATETIME DataTypes

[Geekitygeek]

Aah, i was not aware of that :p the msdn page should really make a note of the sql server versions that support the datatypes on the page i linked that lists them :/

[Lusiphur]

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

Also, when I use the table designer the only date related types are DateTime and SmallDateTime.

That, right there, is why I was recommending DateTime :twisted: MS SQL 2005 is very... uncooperative... when it comes to date/time formats.

[AngelicOne]

Just as I thought.. xP

So is there a best workaround?

[nick.crane]

Use a DateTime and ensure that the time is always set to 00:00:00.
When reading/writing a value use the Date property:
Get myDateTime - SomeValue = myDateTime.Date; Set myDateTime - myDateTime = newDateTime.Date; Set to today - myDateTime = DateTime.Today;