asked on

Query on DateTime

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)

adwiseman

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'

geotiger

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)

ASKER CERTIFIED SOLUTION

Hilaire

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

running32

ASKER

dateofclaim is a datetime field. None of the solutions above worked. Thanks

running32

ASKER

Hilaire, thanks for your help. Just what I needed.

Scott Pletcher

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.