470,590 Members | 2,546 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,590 developers. It's quick & easy.

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

Jul 23 '05 #1
3 9180
(ar*****************@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2
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

Jul 23 '05 #3

Thanks for your great help..
Regards
Arijit Chatterjee

Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Vilen | last post: by
7 posts views Thread by Alberto | last post: by
5 posts views Thread by Adam Knight | last post: by
8 posts views Thread by JJ | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.