Posted by Erland Sommarskog on 06/12/97 11:17

(arijitchatterjee123@yahoo.co.in) writes:
> I am new with SQL Server..I am working with SQL Server 2000.
> I am storing the date in a nvarchar column of atable.... Now I want to
> show the data of Weekends..Everything is OK...But the problem is
> arising with Conversion of nvarchar to date...to identify the
> weekends...Like..Here DATEVALUE is a nvarchar column...But getting the
> error..Value of DATEVALUE like dd-mm-yyyy...04-08-2004

Best is to store date values in datetime columns. If you use character
format, you should use char (the n just doubles the space with no gain
for it, and the var is pointless since size is fixed), and you should use
the format YYYYMMDD. Furthermore, you should attach a constraint to the
columns

datecol char(8) CONSTRAINT ck_tbl_datecol CHECK (isdate(datecol) = 1)

to ascertain that you don't get illegal values.

Storing dates in a format like DD-MM-YYYY is going to give all sorts of
headache. 04-08-2004 could be interpreted as Aug 4th or April 8th, depending
on language and datefromat settings. (And, in case of humans, of the
perceptions of the user.) You can't sort on this format (unless you really
want 3 Aug to come before 4 June).

The format YYYYMMDD sorts well, and is always interpreted in the same way.

See also http://www.karaszi.com/SQLServer/info_datetime.asp.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]