Problem with query??? - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Problem with query???

Author

Topic

nayanancha
Starting Member

27 Posts

Posted - 2010-07-14 : 12:23:35

I need a query for the below scenario.

Date column is nvarchar datatype and DB is SQl Server 05

ID Date
1 07/15/2009
2 07/25/2010
3 06/11/2006

The user will select only month and day i.e mm/dd

so in the report if i select between 07/01 - 07/31 it should return the first two records irrespective of the year.

I mean this query should bring in the records with in that period irrespective of the year.

Thanks

Sachin.Nand

2937 Posts

Posted - 2010-07-14 : 13:03:41

where dateprt(mm,date)=@monthprm and dateprt(dd,date)=@dayprm

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-07-14 : 13:17:31

where datepart(mm,date)=@monthprm and datepart(dd,date)=@dayprm

Everyday I learn something that somebody else already knew

Kristen
Test

22859 Posts

Posted - 2010-07-14 : 13:19:34

quote:
Originally posted by nayanancha

Date column is nvarchar datatype and DB is SQl Server 05

nayanancha
Starting Member

27 Posts

Posted - 2010-07-14 : 14:42:07

Thanks for your suggestion....

But my search parameters has two text boxes where they enter

month and day like mm/dd

so if i want to have records from 06/01 to 08/31 then how should i check for records in between the two dates ( i.e 06/01 to 08/31)

In the above example it works only for date i.e 07/26. With your above query, I am able to

produce the records for one month say 07/01 to 07/31.

But I want to have the records from 06/01 - 08/31.

Thanks

Kristen
Test

22859 Posts

Posted - 2010-07-15 : 02:17:37

"how should i check for records in between the two dates"

Do you mean regardless of Year?

nayanancha
Starting Member

27 Posts

Posted - 2010-07-15 : 03:36:54

quote:
Originally posted by Kristen

"how should i check for records in between the two dates"

Do you mean regardless of Year?

Yes, i want the records regardless of Year but based month and day

Kristen
Test

22859 Posts

Posted - 2010-07-15 : 03:50:23

(Greater than start month or (equal to start month and greater than start day))
AND
(Less than end month or (equal to end month and less than end day))

that will only work if the END is later than START.

If you want to be able to do "between November and February" that will need something more sophisticated

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-15 : 04:14:42

SELECT * FROM Table1
WHERE LEFT(Col1, 5) BETWEEN '07/01' AND '07/31'

N 56°04'39.26"
E 12°55'05.63"

Kristen
Test

22859 Posts

Posted - 2010-07-15 : 04:23:02

WHERE Col1 BETWEEN '07/01' AND '07/31' + 'z'

more index friendly perhaps?

Still the issue with "November to February" ...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-15 : 05:54:47

WHERE Col1 NOT BETWEEN '02/28' AND '11/30'

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-15 : 05:58:22

[code]DECLARE @UserParamFromDay CHAR(5),
@UserParamToDay CHAR(5)

SELECT @UserParamFromDay = '07/01',
@UserParamToDay = '07/31'

-- Peso 1
IF @UserParamFromDay <= @UserParamToDay
SELECT *
FROM Tabl1
WHERE Col1 BETWEEN @UserParamFromDay AND @UserParamToDay
ELSE
SELECT *
FROM Tabl1
WHERE Col1 NOT BETWEEN @UserParamToDay AND @UserParamFromDay

-- Peso 2
SELECT *
FROM Tabl1
WHERE CASE
WHEN @UserParamFromDay <= @UserParamToDay AND Col1 BETWEEN @UserParamFromDay AND @UserParamToDay THEN 1
WHEN @UserParamFromDay > @UserParamToDay AND Col1 NOT BETWEEN @UserParamToDay AND @UserParamFromDay THEN 1
ELSE 0
END = 1[/code]

N 56°04'39.26"
E 12°55'05.63"

Kristen
Test

22859 Posts

Posted - 2010-07-15 : 06:02:34

Yeah, good idea. Or

WHERE Col1 NOT BETWEEN '02/28' AND '11/31' + 'z'

so is this the composite then?


WHERE
(
       '02/28' <= '11/31'
   AND Col1 BETWEEN '02/28' AND '11/31' + 'z'
) OR
(
       '02/28' > '11/31'
   AND Col1 NOT BETWEEN '02/28' AND '11/31' + 'z'
)

obviously substituting @ Parameters for the 'mm/dd' constants above.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-15 : 06:02:46

quote:
Originally posted by Kristen

WHERE Col1 BETWEEN '07/01' AND '07/31' + 'z'

I don't think it's necessary

SET SHOWPLAN_TEXT ON
GO

DECLARE	@Sample TABLE
	(
		Data VARCHAR(10)
	)

INSERT	@Sample
SELECT	'Ab' UNION ALL
SELECT	'Bc' UNION ALL
SELECT	'Cd' UNION ALL
SELECT	'De' UNION ALL
SELECT	'Ef' UNION ALL
SELECT	'Fg' UNION ALL
SELECT	'Gh'

SELECT	*
FROM	@Sample
WHERE	Data BETWEEN 'C' AND 'E'
GO

SET SHOWPLAN_TEXT OFF


  |--Table Scan(OBJECT:(@Sample), WHERE:([Data]>='C' AND [Data]<='E'))

N 56°04'39.26"
E 12°55'05.63"

Kristen
Test

22859 Posts

Posted - 2010-07-15 : 06:14:42

[code]

DECLARE @TestData TABLE
(
Col1 CHAR(10)
)

INSERT INTO @TestData
SELECT '06/30/2010' UNION ALL
SELECT '07/01/2010' UNION ALL
SELECT '07/02/2010' UNION ALL
SELECT '07/30/2010' UNION ALL
SELECT '07/31/2010' UNION ALL
SELECT '08/01/2010'

DECLARE @UserParamFromDay CHAR(5),
@UserParamToDay CHAR(5),
@intLoop INT

SELECT @intLoop = 1

WHILE @intLoop <= 2
BEGIN

IF @intLoop = 1
BEGIN
SELECT @UserParamFromDay = '07/01',
@UserParamToDay = '07/31',
@intLoop = @intLoop + 1
END
ELSE
BEGIN
SELECT @UserParamFromDay = '07/31',
@UserParamToDay = '07/01',
@intLoop = @intLoop + 1
END

SELECT [@UserParamFromDay]=@UserParamFromDay, [@UserParamToDay]=@UserParamToDay
PRINT 'WithOUT ''z'' apended'
SELECT *
FROM @TestData
WHERE
(
@UserParamFromDay <= @UserParamToDay
AND Col1 BETWEEN @UserParamFromDay AND @UserParamToDay
) OR
(
@UserParamFromDay > @UserParamToDay
AND Col1 NOT BETWEEN @UserParamToDay AND @UserParamFromDay
)

PRINT 'With ''z'' apended'
SELECT *
FROM @TestData
WHERE
(
@UserParamFromDay <= @UserParamToDay
AND Col1 BETWEEN @UserParamFromDay AND @UserParamToDay + 'z'
) OR
(
@UserParamFromDay > @UserParamToDay
AND Col1 NOT BETWEEN @UserParamToDay AND @UserParamFromDay + 'z'
)

END -- @intLoop

@UserParamFromDay @UserParamToDay
----------------- ---------------
07/01 07/31

WithOUT 'z' apended
Col1
----------
07/01/2010
07/02/2010
07/30/2010

With 'z' apended
Col1
----------
07/01/2010
07/02/2010
07/30/2010
07/31/2010

@UserParamFromDay @UserParamToDay
----------------- ---------------
07/31 07/01

WithOUT 'z' apended
Col1
----------
06/30/2010
07/31/2010
08/01/2010

With 'z' apended
Col1
----------
06/30/2010
08/01/2010

[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-15 : 07:08:20

Ay ay ay... My original (see post made 07/15/2010 : 04:14:42) "LEFT(Col1, 5)" got lost in the other posts. Good spot, Kristen!

-- Peso 1
IF @UserParamFromDay <= @UserParamToDay 
	SELECT	*
	FROM	Tabl1
	WHERE	LEFT(Col1, 5) BETWEEN @UserParamFromDay AND @UserParamToDay
ELSE
	SELECT	*
	FROM	Tabl1
	WHERE	LEFT(Col1, 5) NOT BETWEEN @UserParamToDay AND @UserParamFromDay

-- Peso 2
SELECT	*
FROM	Tabl1
WHERE	CASE
		WHEN @UserParamFromDay <= @UserParamToDay AND LEFT(Col1, 5) BETWEEN @UserParamFromDay AND @UserParamToDay THEN 1
		WHEN @UserParamFromDay > @UserParamToDay AND LEFT(Col1, 5) NOT BETWEEN @UserParamToDay AND @UserParamFromDay THEN 1
		ELSE 0
	END = 1

N 56°04'39.26"
E 12°55'05.63"

Kristen
Test

22859 Posts

Posted - 2010-07-15 : 07:33:41

Naughty naughty ... not using indexes eh?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-15 : 07:36:51

Relying on HASH aggregates instead.

N 56°04'39.26"
E 12°55'05.63"

Kristen
Test

22859 Posts

Posted - 2010-07-15 : 07:56:09

Ah, of course. Silly me

nayanancha
Starting Member

27 Posts

Posted - 2010-07-15 : 14:34:56

[url]http://berezniker.com/content/pages/sql/microsoft-sql-server/birthday-query-ms-sql-server[/url]

Thanks the above link did the trick for me. I was looking for the same.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-15 : 15:08:04

Oh, so THAT was your original intent of which we were not allowed to know?

N 56°04'39.26"
E 12°55'05.63"

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources