What is wrong with this query and what is the correct syntax

0
August 16, 2011

SELECT OrderID, cdate([OrderDate]), ShipCity FROM [Copy Of dbo_Orders] WHERE (([OrderDate] >= ?) AND ([OrderDate] <=?))

If I enter 10/1/2008 and 10/10/2008 I get back the correct date range BUT I get every record for every year!

[Copy Of dbo_Orders].[OrderDate] is Type Text and I thought doing the conversion with cdate would clean up the results.

[Copy Of dbo_Orders] is a copy of the Orders table from a SQL NORTHWND database.

If I run the query against [dbo_Orders] ([OrderDate] being DateTime) it returns the correct data so it seems the problem comes from [OrderDate] being Text.

27

answers

Comments See all(0)

Add comment
0
August 16, 2011

try query2 or query3

query2  converts to real date

query3  as is (text)

Attachments
0
August 16, 2011

you have the right idea of having to convert but you need to do it in the where clause

SELECT OrderID, cdate([OrderDate]), ShipCity FROM [Copy Of dbo_Orders] WHERE ((cdate([OrderDate])>= ?) AND (cdate([OrderDate])<=?))

0
August 16, 2011

SELECT OrderID, cdate([OrderDate]), ShipCity FROM [Copy Of dbo_Orders] WHERE (([OrderDate] >= [Enter StartDate) AND ([OrderDate] <=[Enter EndDate]))

0
August 16, 2011

SELECT OrderID, cdate([OrderDate]), ShipCity FROM [Copy Of dbo_Orders] WHERE ((Cdate([OrderDate]) >= [Enter StartDate) AND (cdate([OrderDate]) <=[Enter EndDate]))

0
August 16, 2011

Thanks for the replies BUT every record for every year in that date range is returned!!

From both of your suggestions.

Ralph

0
August 16, 2011

CDate() is an Access function.  I do not know the equivalent, or if there is one, in SQL.  What is your OrderDate format in the SQL table - which I presume is still text?

0
August 16, 2011

This is all coming from Access.

[Copy Of dbo_Orders].[OrderDate] is Type Text this fails returning every year.

[dbo_Orders].[OrderDa

te] is Type DateTime this works.

0
August 16, 2011

If your date format is yyyy/mm/dd then you should be save making sure your parameters are string:

SELECT OrderID, [OrderDate], ShipCity FROM [Copy Of dbo_Orders] WHERE [OrderDate] BETWEEN Cstr(EnterStartDate yyyy/mm/dd) AND CStr(EnterEndDate yyyy/mm/dd)

You should really get a bit more informative with your prompts.  If the date format is mm/dd/yyyy, then all bets are off.  yyyy/mm/dd is the only string format for a date where you can use BETWEEN or >= AND <=

0
August 16, 2011

try

SELECT OrderID, CDate([OrderDate]) AS [Order_Date], ShipCity

FROM [Copy Of dbo_Orders]

WHERE (((CDate([OrderDate]))>=[En

ter StartDate] And (CDate([OrderDate]))<=[Ente

r EndDate]));

0
August 16, 2011

What is the actual format of the data in your table.  I asked earlier and never got an answer.

0
August 16, 2011

capricorn1:

SELECT OrderID, CDate([OrderDate]) AS [Order_Date], ShipCity

FROM [Copy Of dbo_Orders]

WHERE (((CDate([OrderDate]))>=[En

ter StartDate] And (CDate([OrderDate]))<=[Ente

r EndDate]));

Results every data for every year in the Table, the month day range is correct but the year is ignored.

GRayL:

the OrderDate looks like 10/1/2008 or mm/dd/yyyy

0
August 16, 2011

GRayL: says

"yyyy/mm/dd is the only string format for a date where you can use BETWEEN or >= AND <="

Now that's interesting. What would I need to do to change the format?

Also if I pull from a DateTime column then BETWEEN and >= AND <= works fine!!

0
August 16, 2011

RalphHxyz,

what is your regional seting?

can you upload a db with that table  [Copy Of dbo_Orders]

check Attach File below.

0
August 16, 2011

Correct but remember 07/08/09 as a string date is greater than 06/07/10 even though the intended format was mm/dd/yy - remember you cannot expect a string date to behave as though it were a date.  Strings sort according to well defined rules and looking like a date ain't one of them!

0
August 16, 2011

I have attached the database with the dbo_Orders and Copy Of dbo_Orders tables.

dbo_Orders.OrderDat

e is DateTime and works.

Copy Of dbo_Orders.OrderDate is Text and fails.

Attachments
0
August 16, 2011

I am not sure about the Query1 I sent up in that database I am working in Access 2008 and still feeling my way around.

Ralph

0
August 16, 2011

try it again.. notice that you uploaded (0 bytes)

0
August 16, 2011

Ok, here goes

Attachments
0
August 16, 2011

use this COPY and PASTE

SELECT OrderID, CDate([OrderDate]) AS [Order_Date], ShipCity

FROM [Copy Of dbo_Orders]

WHERE (((CDate([OrderDate]))>=cda

te([Enter StartDate]) And (CDate([OrderDate]))<=cdate

([Enter EndDate])));

0
August 16, 2011

Here is something I found online re cdate()

CDATE

Returns the date integer for the date specified by the date string in the first argument. The second argument is the date map. If the second argument is omitted, the current value of the system parameter DATE is used as the date map.num = CDATE ( date-string [, date_map])

If this is applicable to Access what is the date_map syntax?

Ralph

0
August 16, 2011

if you don't want to convert to real date you can use this

SELECT [Copy Of dbo_Orders].OrderID, [Copy Of dbo_Orders].OrderDate, [Copy Of dbo_Orders].ShipCity

FROM [Copy Of dbo_Orders]

WHERE (((Format([OrderDate],"yyy

ymmdd"))>=F

ormat([Ent

er StartDate],"yyyymmdd") And (Format([OrderDate],"yyyym

mdd"))<=For

mat([Enter

EndDate],"yyyymmdd")));

0
August 16, 2011

Format() works on a string?

0
August 16, 2011

Well I'll be damned!  Learn something every day here.

0
August 16, 2011

How about CDate(Format([OrderDate],"

mm/dd/yyyy

"))

SELECT [Copy Of dbo_Orders].OrderID, CDate(Format([OrderDate],"

yyyy/mm/dd

")) AS Order_Date, [Copy Of dbo_Orders].ShipCity

FROM [Copy Of dbo_Orders]

WHERE ((([Copy Of dbo_Orders].OrderDate) Between [?] And [?]));

Of course Access drops the other Parameter to get  a Date Range but it does return a correct resultset for a single day.

Ralph

0
August 16, 2011

just take note that

Between [?] And [?]  will take the last value you enter, because they are the same parameter

if you do this

Between [?] And [??]  which is similar to StartDate and EndDate, you will  get the result

just try query2 or query3

0
August 16, 2011

Thank you all, I posted that format in between the answers, sorry.

I am testing now, it looks like formating the OrderDate is what it takes.

This should help to answer my other questions about populating a ASP.NET GridView from a date range.

Thanks again for all your help.

Ralph

0
August 16, 2011

For what it's worth, I just tested and BETWEEN ? and ? provides the query with two separate values, provided of course that different values are entered at each of the prompts.

Related Questions

online form not working

We are using http://www.tectite.com/ for an online form on the below website: http://myrascakes.com/orde r.html We keep getting an error and don&#39;t know how to fix it. &#160;There is a r Read More

Views

818

Votes

0

Answers

15

August 15, 2011

This is the very first question

Hello guys! This is the first post in this site Read More

Views

2k

Votes

4

Answers

2

January 08, 2016

report values not showing up.

I have a crystal report which I pass a DataSet to using VB.NET. The report was working fine, but then I make some changes to the import query and now nothing shows up in the crystal report. Crystal do Read More

Views

1k

Votes

0

Answers

12

September 06, 2005

Event log doesn't overwrite as needed in SP4

Hello All, After upgrading from SP3 to SP4 on several Windows 2000 PRO machines everything looked fine, but from time to time applications can&#39;t write to the event log. When I try to view the appl Read More

Views

1k

Votes

0

Answers

9

May 11, 2003

"Failed to self-register XYZ.dll"

Hi there, I wrote a OLE-automation-server DLL in VB4.0. I use Installshield Express to install it as part of my program on the target computers. Now on some computers I get the message &quot;Failed to Read More

Views

2k

Votes

0

Answers

2

November 08, 1998

Please help understand these notes on image processing

Can someone please help me understand these lecture notes... On the right of the page; What does &quot;normalised by one notion of the area of a pixel&quot; mean? On the first formula for A, Is that a Read More

Views

1k

Votes

0

Answers

3

February 05, 2009

Can someone please explain this paragram on the chain rule in image processing

Please see the screenshot, How is 3133030 got from 10103322? Read More

Views

1k

Votes

0

Answers

1

January 05, 2009

AD on 2003

We have created an AD Domain on Windows 2000 Server with no problems. We just created an AD Domain on Windows 2003 and we&#39;re getting some weird problems. Both of these domains are behind firewalls Read More

Views

1k

Votes

0

Answers

15

May 11, 2003

ADDT ASP Upload Error " Type mismatch: 'tNG_isFileInsideBaseFolder' "

I am trying to create a simple insert record and upload image function on an ASP page built using Adobe Dreamweaver Developer Toolkit. I have done this many times before with no problem, however, i ha Read More

Views

1k

Votes

0

Answers

0

November 02, 2008

MYSQL Select query with custom ORDER BY

Hi, is it possible to customize the order of the returned rows in mysql? Example: I have a Table with a column &quot;name&quot;, now I want to have all entries ordered by name, but I want the entries Read More

Views

1k

Votes

0

Answers

5

July 02, 2010

Folder Redirection in Server 2003

We have a Windows Server 2003 Standard Ed. Is there a way to redirect the users my documents to a folder that has already been created on the server? Read More

Views

1k

Votes

0

Answers

2

July 05, 2007

Partiton magic  version 8.0 having error 1523 while executing batch

I followed the partition magic 8.0 wizard to partition my hard disk while rebooting &#160;I came across this problem &#160;&quot;error 1523 while executing batch&quot; What can I do to solve this prob Read More

Views

1k

Votes

0

Answers

2

May 11, 2003

Please explain this paragraph in image processing (screenshot attached)

Two questions; How is the equation (10.1-15) formed? &quot;are isotropic for rotation increments of 90 degrees and 45 degrees respectively&quot; What does this mean Read More

Views

774

Votes

0

Answers

7

January 05, 2009

xp_cmdshell with net use

Hi, when I use &#39;net use \192.168.0.1\c$ password /user:username&#39; at the command prompt, it works fine. &#160;But if i try to use it in sql with &quot; exec master..xp_cmdshell &#39;net use \19 Read More

Views

723

Votes

0

Answers

5

April 11, 2003

hp ux11

i have a hp ux11 server. and i edited the /etc/resolv.conf to use my win2003 server dns server. That went fine, however, I would like to go the other way and add an entry on my win2003 dns to resolve Read More

Views

413

Votes

0

Answers

3

October 12, 2009

Using an application over a LAN

I have written an application that needs to be used on a LAN. &#160;The application setup wizard takes care of registering everything on a single PC, but how do you go about writing a setup program th Read More

Views

361

Votes

0

Answers

9

August 07, 1998

Need subnet of 12 IPs.

Please, Can someone help me to create a subnet consisting of 12 IP&#39;s. Thanks.. Read More

Views

369

Votes

0

Answers

5

April 11, 2003

Drill down in a cross-tabl report

Hello, Would some experts to show me if it is possible to do drill-up or drill-down (or both) in a cross-tab report? I would also like to know if it is possible to control what fields to be displayed/ Read More

Views

298

Votes

0

Answers

0

September 06, 2005

Using RDO, Enterprise V Professional

We would like to use RDO on our project made up of 4 developers. Do we need to have a copy of Enterprise for each developer or is it possible to use Professional for those developers not writing the R Read More

Views

350

Votes

0

Answers

6

January 07, 1998

Can't Edit IIS Metabase.xml

We do this on every windows 2003 server and never had an issue. We cannot edit the IIS Metabase.xml We have stopped IIS and made change click save, no problem but it does not take the new information. Read More

Views

352

Votes

0

Answers

0

July 05, 2007