Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 24th, 2007, 07:50 AM
Registered User
 
Join Date: Aug 2007
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default convert data from nvarchar to Date Time

I get a problem with this query

SELECT * FROM RG_23CI WHERE BILL_DATE > '03/31/2007'

IN TABLE RG_23CI DATATYPE OF BILL_DATE IS NVARCHAR SO THIS QUERY DON'T SHOW THE RIGHT DATA SO THEN I TRY TO CONVERT THE BILL_DATE IN TO SMALLDATETIME OR DATETIME FORMAT WITH THIS QUERY

SELECT * FROM RG_23CI WHERE CAST(BILL_DATE AS DATETIME) > '03/31/2007'

THIS QUERY GIVES AN ERROR

(4 row(s) affected)

Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.


So please help me to solve this problem

 
Old August 24th, 2007, 09:11 AM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

This will work:
SELECT * FROM RG_23CI WHERE Convert(varchar(10), BILL_DATE, 101) > '03/31/2007'

And so will this:
SELECT * FROM RG_23CI WHERE Convert(datetime, BILL_DATE) > '03/31/2007'


================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for:
Professional Search Engine Optimization with ASP.NET

Professional IIS 7 and ASP.NET Integrated Programming

================================================== =========
 
Old August 24th, 2007, 09:29 AM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

Quote:
quote:Originally posted by rahulgzb
 I get a problem with this query

SELECT * FROM RG_23CI WHERE BILL_DATE > '03/31/2007'

IN TABLE RG_23CI DATATYPE OF BILL_DATE IS NVARCHAR SO THIS QUERY DON'T SHOW THE RIGHT DATA SO THEN I TRY TO CONVERT THE BILL_DATE IN TO SMALLDATETIME OR DATETIME FORMAT WITH THIS QUERY

SELECT * FROM RG_23CI WHERE CAST(BILL_DATE AS DATETIME) > '03/31/2007'

THIS QUERY GIVES AN ERROR

(4 row(s) affected)

Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.


So please help me to solve this problem

There might be something going on... would you please post the results from the following???

SELECT TOP 10 BILL_DATE
FROM RG_23CI


--Jeff Moden
 
Old August 24th, 2007, 01:27 PM
Friend of Wrox
 
Join Date: May 2006
Posts: 246
Thanks: 0
Thanked 0 Times in 0 Posts
Default

What is your SET DATEFORMAT setting?


 
Old September 7th, 2007, 09:45 PM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

Heh... you were saying something about a new breed of Yaks? I'm going to stop asking these rubbers if they need help :D

--Jeff Moden





Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert from nvarchar(25) to smalldatetime aspless SQL Language 6 September 14th, 2007 09:21 AM
convert nvarchar to money smatyk SQL Server 2000 2 October 26th, 2006 06:37 PM
Convert int to nvarchar bekim SQL Language 1 August 12th, 2004 06:33 AM
Convert Text to Date/Time in Access mmcdonal Access VBA 7 June 15th, 2004 12:19 PM
Date based query when date is nvarchar MichaelTJ SQL Language 4 January 12th, 2004 09:57 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.