Conversion Error...nvarchar to Datetime

Hi Group,
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

------------------
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
------------------
------Actual Query----------
Select DATEVALUE,<Other Column Names> from Result where
Datepart(dw,convert(Datetime,DATEVALUE))<>1 and
Datepart(dw,convert(Datetime,DATEVALUE))<>7
------------------
Thanks in advance..
Regards
Arijit Chatterjee(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/techin.../2000/books.asp|||See this

declare @.t table (d varchar(20))
insert into @.t values('10-apr-2005')
insert into @.t values('10-MAy-2005')
insert into @.t values('10-Jun-2005')
insert into @.t values('10-Jul-2005')
Select * from @.t where Datepart(dw,convert(Datetime,d))<>1 and
Datepart(dw,convert(Datetime,d))<>7

Madhivanan|||Thanks for your great help..
Regards
Arijit Chatterjee