SELECT and dates question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SELECT and dates question

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2007-09-30 : 11:20:16

My table contains three records as an example

item boxes quantity date
CH 20 16 10/09/2007
CH 10 20 11/09/2007
CH 15 16 12/09/2007

using the request:

SELECT item, sum(boxes), quantity FROM transactions WHERE (item = 'CH' AND date_recvd BETWEEN '09-09-2007' AND '12-09-2007') GROUP BY item, quantity

I get:

CH 35 16
CH 20 10

but if I change my date parameters to:

SELECT item, sum(boxes), quantity FROM transactions WHERE (item = 'CH' AND date_recvd BETWEEN '09-09-2007' AND '10-09-2007') GROUP BY item, quantity

I still get:

CH 35 16
CH 20 10

My query syntax is obviously incorrect. What should it be please?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-30 : 13:04:47

Looks like the dateformat is different. use
Set Dateformat MDY -- or DMY as appropriate.

Also, change the query as:


SELECT item, sum(boxes), quantity 
FROM transactions 
WHERE (item = 'CH' AND date_recvd >= '20070909' AND date_recvd <= '20071209') 
GROUP BY item, quantity

The format should be 'YYYYMMDD'.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

nr
SQLTeam MVY

12543 Posts

Posted - 2007-09-30 : 13:10:33

Set Dateformat MDY -- or DMY as appropriate.
Won't help if you use yyyymmdd.
What datatype is date_recvd?
If it's atetime the the above query will work - if not you should convert it using the appropriate style.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-03 : 03:35:10

As asked, what is the datatype of the date column?
Always use proper DATETIME datatype to store dates

Madhivanan

Failing to plan is Planning to fail

renu
Starting Member

47 Posts

Posted - 2007-10-03 : 04:58:48

this query is working fine:
SELECT item,sum(boxes), quantity FROM tablename
WHERE (item = 'CH' AND date BETWEEN '09-09-2007' AND '10-09-2007')
GROUP BY item, quantity

output:
CH 20 16

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-03 : 05:06:56

quote:
Originally posted by renu

this query is working fine:
SELECT item,sum(boxes), quantity FROM tablename
WHERE (item = 'CH' AND date BETWEEN '09-09-2007' AND '10-09-2007')
GROUP BY item, quantity

output:
CH 20 16

It depends on the dateformat of the server
As said, always use YYYYMMDD format

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources