Prev: SMS 2003 report help
Next: Chart Question
From: Chamark via SQLMonster.com on 25 Feb 2010 11:38
Using SQL 2005

Is there a way to query a date range with only one date provided? The date
field is a timestamp with mm/dd/yyyy mm:ss. User provides date from a
dropdown list on a form - example 12/01/2009. We want to provide all records
that fall in the month of Dec 2009.

SELECT [DATE]
FROM tbl
WHERE [DATE] >= '12/01/2009' ???

How to get just the dates for that month?

Your help is greatly appreciated

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/201002/1

From: Plamen Ratchev on 25 Feb 2010 12:11
You can calculate range from first of the month and first of the next month based on the date:

DECLARE @dt DATETIME;

SET @dt = '20091201';

SELECT [DATE]
FROM tbl
WHERE [DATE] >= DATEADD(MONTH, DATEDIFF(MONTH, '20010101', @dt), '20010101')
AND [DATE] < DATEADD(MONTH, DATEDIFF(MONTH, '20010101', @dt), '20010201');

--
Plamen Ratchev
http://www.SQLStudio.com
From: Chamark via SQLMonster.com on 25 Feb 2010 12:29
Hey Plamen thanks for your help - but this need to be automated based on the
single month/year provided. Is there some way of taking part of the date
provided, say month & year and determining that we want only records in that
month without having to provide the end date?

Plamen Ratchev wrote:
>You can calculate range from first of the month and first of the next month based on the date:
>
>DECLARE @dt DATETIME;
>
>SET @dt = '20091201';
>
>SELECT [DATE]
>FROM tbl
>WHERE [DATE] >= DATEADD(MONTH, DATEDIFF(MONTH, '20010101', @dt), '20010101')
> AND [DATE] < DATEADD(MONTH, DATEDIFF(MONTH, '20010101', @dt), '20010201');
>

--
Message posted via http://www.sqlmonster.com

From: Plamen Ratchev on 25 Feb 2010 12:40
This is exactly what the code I posted does, determines the range based on a single date (@dt). There is no need to
provide end date (for that matter no need for start date, just any date withing the month will do).

--
Plamen Ratchev
http://www.SQLStudio.com
From: Chamark via SQLMonster.com on 25 Feb 2010 12:50
OK thanks again

Plamen Ratchev wrote:
>This is exactly what the code I posted does, determines the range based on a single date (@dt). There is no need to
>provide end date (for that matter no need for start date, just any date withing the month will do).
>

--
Message posted via http://www.sqlmonster.com

 | 
Pages: 1
Prev: SMS 2003 report help
Next: Chart Question