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 7 1536
"ndn_24_7" <nd******@yahoo .com> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.com.. . 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
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
In addition to Simon's comments, have a look at http://www.aspfaq.com/show.asp?id=2280
-Andy
"ndn_24_7" <nd******@yahoo .com> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.com.. . 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
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
ALTER TABLE incident ADD DateOccured datetime
UPDATE incident SET DateOccured = CAST(doccur AS datetime)
"ndn_24_7" <nd******@yahoo .com> wrote in message
news:11******** *************@f 14g2000cwb.goog legroups.com... 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
One more question. Would I have to change the dates to 2004-02-21 in
order to change the datatype to datetime?
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.
ndn_24_7 (nd******@yahoo .com) writes: 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
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, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Keith |
last post by:
I am fairly new to SQL so sorry if this is a really dumb question.
I have a small (still) SQL database, which I am trying to query from an ASP
page.
The field I am querying is of DATETIME data type, and is populated
automatically using the GetDate() function as a default value.
When I try and search on this field, using a date/time in the format
dd/mm/yyyy hh:mm:ss as the search criteria, it fails with the following
|
by: jhweb |
last post by:
I am trying to query a table in oracle from Access 2000 using the
following criteria.
select * from table1 where DATE_RECEIVED between '1/jan/2003' and
'30/jun/2003'
I am using the Oracle odbc driver.
Records should be returned but nothing.
|
by: Marc Pelletier |
last post by:
Hello,
I have a table with a Day field, defined as smalldatetime. I am filling it
from a CSharp application with the following code:
DataRow r = dtStaDays.NewRow();
r= station_ID;
r = sd.Date;
r = rangeTide;
etc.
|
by: AndyBell |
last post by:
Hi all!
I have an Access 2000 database for the Habiat for Humanity where I work.
This is the second database I have written and it gets a bit more complex
each time... I have learned much and read much over the last few months and
have found leads to the answers to several other problems I have encountered
with a Google search of past posts/threads.
SO thank you for the help already rendered even though you didn't know!!
My problem...
|
by: Stewart Allen |
last post by:
I'm trying to filter a table that has 2 date fields, the first date will
always have a value but the second will only occasionally has a value. Each
date field also has a corresponding text field to record the record's
status.
Sample data:
*tblTest*
Model Parts CDate CStatus RDate RStatus
616 $359.79 03-Nov-03 C
| |
by: roiavidan |
last post by:
Hi,
I'm having a bit of a problem with a small application I wrote in C#,
which uses an Access database (mdb file) for storing financial data.
After looking for a similiar topic and failing to find one, I'm posting
the question in hope some one of you guys out there will have the
answer for me...!
I'll start with what I have, then I'll continue to the problem itself.
|
by: Cintury |
last post by:
Ok the problem originally started with me wanting to compare the dates entered on one form with the dates entered on a different form's subform (I hope that wasn't too confusing). Thru numerous trials and error, I discovered that it was not possible to do it this way since 1) the other subform had to be open and 2) the main form of the searchee subform had to be on the correct record already (headache!).
Then I stumbled onto recordsets. I...
|
by: ilikebirds |
last post by:
My table looks like the following
, ,,
Ex.
, , ,
, ,
It contains all the and that goes through in the past 3 months.
How would I go about querying for all 's that took more than 1 day to move from Step 1 to Step 2?... so on and so forth.
|
by: divyac |
last post by:
I am doing a project on inventory control of grocery items using PHP and mysql 5.In my project i have 2 tables..
1.master_in_outs->where all the inward and outward transactions of particular items are inserted
The fields are->id(primary key)
Item_id
Date
transaction(whether inward/outward)
Price
Quantity
Total_amount
Average
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| |
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |