Querying date

Hello all,
I'm trying to run a query to make a report. My database is a incident
reporting database. I'm tryng to make a monthy report for incidents.
The field I need to query in the date field which is a nvarchar in the
form of 01/01/04 and 01/01/2004. I ran a query that looks like this:
SELECT incident, doccur, ID
FROM dbo.Incident
WHERE (doccur between '01/01/2004' and '01/31/2004')

I get some results that look like this:

Unsecured doors 01/19/04 92
INTOXICATION 01/17/04 77
Bill Door entry door 01/28/03 130
Hit & Run 01/21/04 105
Customer complaint 01/02/03 70
Customer complaint 01/02/04 91
PRINTER MALFUNCTION 01/22/04 111
Customer complaint 01/30/04 2322
Trash Smoldering 01/15/04 51
LOST 01/02/03 80
BROKEN GLASS PANEL 01/13/04 42
B.I.A. Assist 01/04/03 189
GAS LEAK 01/06/04 8
UNCHANGED CASH BOX 01/11/04 40
Intoxication 01/17/04 69
Intoxication 01/02/04 71
Intoxication 01/17/04 72
Employee accident 01/17/04 73
GREASE FIRE 01/18/04 74
Verbal Dispute 01/17/04 75
PANHANDLING 01/17/04 76
Near Miss/Water backup 01/18/04 78
Unsecured Arcade Door 01/19/04 93
Intoxication 01/18/04 79
Intoxication 01/02/04 81
SUSPECT/WANTED 01/18/04 82
Intoxication 01/18/04 83
Property Damage 01/20/03 84
Unsecured Bingo Snack Bar 01/18/04 85
PANHANDLING 01/18/04 86
Employee accident 01/19/04 87
Unauthorize of proper exit 01/19/04 88
Safety Hazard 01/19/04 89
Key control violation 01/02/03 90
Cracked keno ball 01/23/04 116
Employee accident 01/19/04 94
delay in drop 01/27/2003 128
test 01/01/2005 3763



As you can see, the querey will give me the month and day I ask for,but
not the right year. Some to the data has 2 digit years and some have 4
digits. How do I design the query to give me the year I ask for.
Any assistance will be greatly appreciated

re: Querying date

"ndn_24_7" <ndn_24_7@yahoo.com> wrote in message
news:1105646861.118444.238060@f14g2000cwb.googlegr oups.com...[color=blue]
> Hello all,
> I'm trying to run a query to make a report. My database is a incident
> reporting database. I'm tryng to make a monthy report for incidents.
> The field I need to query in the date field which is a nvarchar in the
> form of 01/01/04 and 01/01/2004. I ran a query that looks like this:
> SELECT incident, doccur, ID
> FROM dbo.Incident
> WHERE (doccur between '01/01/2004' and '01/31/2004')
>
> I get some results that look like this:
>
> Unsecured doors 01/19/04 92
> INTOXICATION 01/17/04 77
> Bill Door entry door 01/28/03 130
> Hit & Run 01/21/04 105
> Customer complaint 01/02/03 70
> Customer complaint 01/02/04 91
> PRINTER MALFUNCTION 01/22/04 111
> Customer complaint 01/30/04 2322
> Trash Smoldering 01/15/04 51
> LOST 01/02/03 80
> BROKEN GLASS PANEL 01/13/04 42
> B.I.A. Assist 01/04/03 189
> GAS LEAK 01/06/04 8
> UNCHANGED CASH BOX 01/11/04 40
> Intoxication 01/17/04 69
> Intoxication 01/02/04 71
> Intoxication 01/17/04 72
> Employee accident 01/17/04 73
> GREASE FIRE 01/18/04 74
> Verbal Dispute 01/17/04 75
> PANHANDLING 01/17/04 76
> Near Miss/Water backup 01/18/04 78
> Unsecured Arcade Door 01/19/04 93
> Intoxication 01/18/04 79
> Intoxication 01/02/04 81
> SUSPECT/WANTED 01/18/04 82
> Intoxication 01/18/04 83
> Property Damage 01/20/03 84
> Unsecured Bingo Snack Bar 01/18/04 85
> PANHANDLING 01/18/04 86
> Employee accident 01/19/04 87
> Unauthorize of proper exit 01/19/04 88
> Safety Hazard 01/19/04 89
> Key control violation 01/02/03 90
> Cracked keno ball 01/23/04 116
> Employee accident 01/19/04 94
> delay in drop 01/27/2003 128
> test 01/01/2005 3763
>
>
>
> As you can see, the querey will give me the month and day I ask for,but
> not the right year. Some to the data has 2 digit years and some have 4
> digits. How do I design the query to give me the year I ask for.
> Any assistance will be greatly appreciated
>[/color]

The use of nvarchar for dates is the real issue - if you clean up the dates
and change the data type to datetime, then you should have no problems, and
you will be able to use date functions correctly with the data. I appreciate
of course that you may have little control over the data model, but as
you've already seen, using the wrong data type just leads to incorrect or
inconsistent data, so it would be worth some effort to correct this.

The following query may work, but it depends on the quality of your data:

SELECT incident, doccur, ID
FROM dbo.Incident
WHERE cast(doccur as datetime) between '20040101' and '20040131'

Note that the format YYYYMMDD is always interpreted correctly by MSSQL,
whereas the US date format is ambiguous.

Simon

re: Querying date

In addition to Simon's comments, have a look at
http://www.aspfaq.com/show.asp?id=2280

-Andy

"ndn_24_7" <ndn_24_7@yahoo.com> wrote in message
news:1105646861.118444.238060@f14g2000cwb.googlegr oups.com...[color=blue]
> Hello all,
> I'm trying to run a query to make a report. My database is a incident
> reporting database. I'm tryng to make a monthy report for incidents.
> The field I need to query in the date field which is a nvarchar in the
> form of 01/01/04 and 01/01/2004. I ran a query that looks like this:
> SELECT incident, doccur, ID
> FROM dbo.Incident
> WHERE (doccur between '01/01/2004' and '01/31/2004')
>
> I get some results that look like this:
>
> Unsecured doors 01/19/04 92
> INTOXICATION 01/17/04 77
> Bill Door entry door 01/28/03 130
> Hit & Run 01/21/04 105
> Customer complaint 01/02/03 70
> Customer complaint 01/02/04 91
> PRINTER MALFUNCTION 01/22/04 111
> Customer complaint 01/30/04 2322
> Trash Smoldering 01/15/04 51
> LOST 01/02/03 80
> BROKEN GLASS PANEL 01/13/04 42
> B.I.A. Assist 01/04/03 189
> GAS LEAK 01/06/04 8
> UNCHANGED CASH BOX 01/11/04 40
> Intoxication 01/17/04 69
> Intoxication 01/02/04 71
> Intoxication 01/17/04 72
> Employee accident 01/17/04 73
> GREASE FIRE 01/18/04 74
> Verbal Dispute 01/17/04 75
> PANHANDLING 01/17/04 76
> Near Miss/Water backup 01/18/04 78
> Unsecured Arcade Door 01/19/04 93
> Intoxication 01/18/04 79
> Intoxication 01/02/04 81
> SUSPECT/WANTED 01/18/04 82
> Intoxication 01/18/04 83
> Property Damage 01/20/03 84
> Unsecured Bingo Snack Bar 01/18/04 85
> PANHANDLING 01/18/04 86
> Employee accident 01/19/04 87
> Unauthorize of proper exit 01/19/04 88
> Safety Hazard 01/19/04 89
> Key control violation 01/02/03 90
> Cracked keno ball 01/23/04 116
> Employee accident 01/19/04 94
> delay in drop 01/27/2003 128
> test 01/01/2005 3763
>
>
>
> As you can see, the querey will give me the month and day I ask for,but
> not the right year. Some to the data has 2 digit years and some have 4
> digits. How do I design the query to give me the year I ask for.
> Any assistance will be greatly appreciated
>[/color]

re: Querying date

Thank you for your reply,

I would agree that nvarchar for my date is a problem. Would anybody
know how to convert the dates to 4 digit years thru a query or do I
have to go change every date manually?After I change them to the
correct format, I'm assuming I can change the datatype to datetime.
Some of my date are 02/21/04 and some are 02/21/2004. The program has a
Access front end, So i guess I could use the find and replace function.
Any other suggestions will really help

re: Querying date

ALTER TABLE incident ADD DateOccured datetime

UPDATE incident SET DateOccured = CAST(doccur AS datetime)


"ndn_24_7" <ndn_24_7@yahoo.com> wrote in message
news:1105648821.121164.51910@f14g2000cwb.googlegro ups.com...[color=blue]
> Thank you for your reply,
>
> I would agree that nvarchar for my date is a problem. Would anybody
> know how to convert the dates to 4 digit years thru a query or do I
> have to go change every date manually?After I change them to the
> correct format, I'm assuming I can change the datatype to datetime.
> Some of my date are 02/21/04 and some are 02/21/2004. The program has a
> Access front end, So i guess I could use the find and replace function.
> Any other suggestions will really help
>[/color]

re: Querying date

One more question. Would I have to change the dates to 2004-02-21 in
order to change the datatype to datetime?

re: Querying date

I was able to convert all my dates to datetime. Now I can query by
date and all information is accurate.Thank you all so much for you
assistance.I greatly appreciat it.

re: Querying date

ndn_24_7 (ndn_24_7@yahoo.com) writes:[color=blue]
> I would agree that nvarchar for my date is a problem. Would anybody
> know how to convert the dates to 4 digit years thru a query or do I
> have to go change every date manually?After I change them to the
> correct format, I'm assuming I can change the datatype to datetime.
> Some of my date are 02/21/04 and some are 02/21/2004. The program has a
> Access front end, So i guess I could use the find and replace function.
> Any other suggestions will really help[/color]

It seems that you have sorted out your dates by now, but nevertheless
some addditional information.

Like many other programs, SQL Server plays some guessing games based
on some settings. In case of SQL Server these are dateformat and
language, which are peculiare to SQL Server and not the regional
settings. '02/21/04' may give you what you want, it may give you
a different date in a different year, and you might even end up in
the wrong century, if you have a funny configuration of your server.

Anyway, here is an article that gives you the full story:
http://www.karaszi.com/SQLServer/info_datetime.asp.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp