Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

compare varchar in a query as a datetime

Posted on 2010-01-12
7
Medium Priority
?
404 Views
Last Modified: 2012-05-08
table was design a while ago as:

Tab
{
   id              uniqueidentifier not null,
   name        varchar(64),
   solddate        varchar(64),
   type          int
}

this query fails:
select count(*) as AssetsSold from Tab
where isdate(solddate) = 1
and solddate is not null
and convert(datetime,solddate,101)
between convert(datetime,'10/1/2009',101) and convert(datetime,'1/1/2010',101)

with the following error:
"Conversion failed when converting datetime from character string."


how can I pull these (varchar) values and compare them as dates?
0
Comment
Question by:ipaman
7 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26298086
whats the dateformat on solddate  
0
 
LVL 27

Accepted Solution

by:
Chris Luttrell earned 300 total points
ID: 26298819
try this, it checks solddate in that last where condition again, that is where you problem was.
select count(*) as AssetsSold from Tab
where isdate(solddate) = 1
and solddate is not null
and convert(datetime,CASE WHEN isdate(solddate) = 1 THEN solddate ELSE NULL END,101) 
between convert(datetime,'10/1/2009',101) and convert(datetime,'1/1/2010',101)

Open in new window

0
 
LVL 15

Expert Comment

by:MohammedU
ID: 26298902
Try the following...

select convert(char(10),solddate,101) as AssetsSold from AssetsSold
where isdate(solddate) = 1
and solddate is not null
and convert(char(10),solddate,101)
between convert(char(10),'10/1/2009',101) and convert(char(10),'1/1/2010',101)
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 32

Expert Comment

by:awking00
ID: 26303444
You apparently have data in the solddate field that is not in mm/dd/yyyy format. What does the following return?
select convert(datetime,solddate,101) from tab
0
 
LVL 32

Expert Comment

by:awking00
ID: 26303456
Is it possible you have null values in the solddate field?
0
 

Author Comment

by:ipaman
ID: 26304069
ok:
aneeshattingal: date format is not always known but typically mm/dd/yyyy

MohammedU: your returns no rows, even with different date values (that I know are valid)

awking00: yours returns many rows then errors when it htis a unusual format: Conversion failed when converting datetime from character string.
also, I am checking for nulls in my original query.

CGLuttrell: Yours returns a valid number. I suspect I am not getting all the dates because of the varying formats. probably nothing I can do about it. The query is probably the best in this circumstance.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26304531
ipaman,
check whether your table contain invalid date values

select *
from tablename
where isdate(solidDate) = 0


Aneesh
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question