?
Solved

Query on DateTime

Posted on 2005-04-28
6
Medium Priority
?
428 Views
Last Modified: 2010-03-19
I have a query to select from a date range.  Problem is the date range is not working.  the date format in the database is set up at  2002-11-20 00:00:00.000  How can I get the date range.

Thanks


SELECT distinct   ins.insid, ins.insuredid, ins3.dateofclaim
FROM        ddb_insured_base ,   ddb_claim_base as ins3
inner join  ddb_insured_base as ins on  ins.insid = ins3.insid

WHERE     (ins.INSID = '1000003') OR
                      (ins.INSID = '1000004') OR
                      (ins.INSID = '1000005') OR
                      (ins.INSID = '1000006') OR
                      (ins.INSID = '1000012') OR
                      (ins.INSID = '1000013')
and dateofclaim between convert(date,'08-11-2004',110) AND convert(date,'08-19-2004',110)
0
Comment
Question by:running32
6 Comments
 
LVL 14

Expert Comment

by:adwiseman
ID: 13886456
Is the datatype of dateofclaim a datetime or a varchar Datatype, it should be a datetime, or you have to convert it to a datetime before evaluating it.

Also convert(date,'08-11-2004',110)
should be convert(datetime,'08-11-2004')
You don't need the format, it's only used when converting dates to varchar, char, etc...
In addition, if dateofclaim is a datetime datatype, you don't have to convert the dates in the between statement to datetime.

Try this:

WHERE     (ins.INSID = '1000003') OR
                      (ins.INSID = '1000004') OR
                      (ins.INSID = '1000005') OR
                      (ins.INSID = '1000006') OR
                      (ins.INSID = '1000012') OR
                      (ins.INSID = '1000013')
and CONVERT(DATETIME, dateofclaim) between '08-11-2004' AND '08-19-2004'
0
 
LVL 12

Expert Comment

by:geotiger
ID: 13886489
Use DateTime:


SELECT distinct   ins.insid, ins.insuredid, ins3.dateofclaim
FROM        ddb_insured_base ,   ddb_claim_base as ins3
inner join  ddb_insured_base as ins on  ins.insid = ins3.insid

WHERE     (ins.INSID = '1000003') OR
                      (ins.INSID = '1000004') OR
                      (ins.INSID = '1000005') OR
                      (ins.INSID = '1000006') OR
                      (ins.INSID = '1000012') OR
                      (ins.INSID = '1000013')
and dateofclaim between convert(datetime,'08-11-2004',110) AND convert(datetime,'08-19-2004',110)
0
 
LVL 26

Accepted Solution

by:
Hilaire earned 2000 total points
ID: 13886777
Please try

SELECT distinct   ins.insid, ins.insuredid, ins3.dateofclaim
FROM        ddb_insured_base ,   ddb_claim_base as ins3
inner join  ddb_insured_base as ins on  ins.insid = ins3.insid
WHERE ins.INSID IN('1000003', '1000004', '1000005', '1000006', '1000012', '1000013')
and dateofclaim between convert(date,'08-11-2004',110) AND convert(date,'08-19-2004',110)

This syntax is less verbose AND is will much better I guess :

Due to LOGICAL OPERATOR PRECEDENCE, AND has a higher precedence than OR, so

where a = 1 or a = 2 or a = 3 and b = 0
is exactly the same than
where a = 1 or a = 2 or (a = 3 and b = 0)

so if you meant
where (a = 1 or a = 2 or a = 3) and b = 0
use either this syntax with the parens, or the shorter version
where a IN (1,2,3) and b = 0

0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

Author Comment

by:running32
ID: 13886780
dateofclaim is a datetime field.  None of the solutions above worked.  Thanks
0
 

Author Comment

by:running32
ID: 13887150
Hilaire, thanks for your help.  Just what I needed.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 13887167
If "dateofclaim" is a datetime rather than smalldatetime -- and you seem to have indicated that it was -- try this:


WHERE    ( (ins.INSID = '1000003') OR
                      (ins.INSID = '1000004') OR
                      (ins.INSID = '1000005') OR
                      (ins.INSID = '1000006') OR
                      (ins.INSID = '1000012') OR
                      (ins.INSID = '1000013') )
and dateofclaim between '2004-08-11' AND '2004-08-19 23:59:59.997'


By default the time will be 00:00, which may be excluding some rows on the last late.

Note that ".997" is not a typo: SQL can't handle accuracy down to 1ms only 3ms, so if you try to use ".999" it rounds up and may pick up something from the next day.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

864 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