select/where based on date, advice needed

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

SQL Server 2005 Forums

Transact-SQL (2005)

select/where based on date, advice needed

Author

Topic

Fatboy40
Starting Member

8 Posts

Posted - 2008-06-02 : 07:14:34

I'm very green with SQL so I could do with some advice please.

I need to pull some data from a table based on the year portion of a datetime field, so far I've got this...

USE MfgSys803

SELECT orderdate, ordernum FROM orderhed

WHERE ((SELECT CONVERT(VARCHAR(4),GETDATE(),111)) = (SELECT CONVERT(VARCHAR(4),ORDERDATE,111) FROM orderhed))

... the field 'orderdate' is the datetime. The purpose of the WHERE statement is to get the current year fromt he system and then compare this to the current year of the field 'orderdate'.

Unfortunately I get the error...

Msg 512, Level 16, State 1, Line 3
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

... the 'SELECT CONVERT' portions of the WHERE work fine on thier own but I can't use them together.

Hopefully this makes sense, thanks :)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-06-02 : 07:18:09

[code]SELECT orderdate, ordernum FROM orderhed
where dateadd(day, datediff(day, 0, getdate()), 0) = dateadd(day, datediff(day, 0, OrderDate), 0)[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-02 : 07:19:03

[code]USE MfgSys803

SELECT orderdate,
ordernum
FROM orderhed
WHERE CONVERT(VARCHAR(4), GETDATE(), 111) = CONVERT(VARCHAR(4), ORDERDATE, 111)[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-02 : 07:20:44

Harsh, he want all records for current year.
Style 111 is YYYY/MM/DD which makes OP return "2008/" for all records this year.
I personally thinks this is a bug.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-02 : 07:23:04

Use this for faster querying. With this you can use the present index over orderdate.

USE MfgSys803

DECLARE	@FromDate DATETIME,
	@ToDate DATETIME

SELECT	@FromDate = DATEADD(YEAR, DATEDIFF(YEAR, '19000101', getdate()), '19000101'),
	@ToDate = DATEADD(YEAR, 1, @FromDate)

SELECT	orderdate,
	ordernum
FROM	orderhed
WHERE	ORDERDATE >= @FromDate
	AND ORDERDATE < @ToDate

E 12°55'05.25"
N 56°04'39.16"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-06-02 : 07:26:06

quote:
Originally posted by Peso

Harsh, he want all records for current year.
Style 111 is YYYY/MM/DD which makes OP return "2008/" for all records this year.
I personally thinks this is a bug.

E 12°55'05.25"
N 56°04'39.16"

Oh..good catch!