Selecting Records based on the date range

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 2000 Forums

Transact-SQL (2000)

Selecting Records based on the date range

Author

Topic

stsj
Starting Member

9 Posts

Posted - 2003-04-02 : 06:32:11

i want to select the values based on the selected year month
i have one table with RegisterDate field and site id.
It is datetime and data is store in that as 01/12/2000 so on
If User selects from date as 03/1999 and to date as 04/2000
i have to selects the records where RegisterDateis between 03/1992 and 04/1994
but if i give that it is not showing me any values

this is the query ..i have records in the table with the values in
registerdate field which fall under the range...so how to build the
sql statement to get those records...
like i want to show the records where RegisterDate = 03/1992,04/1992 ....
01/1993,12/1993....01/1994...because all these will fall under the selected range.

SELECT a.site_id,a.RegisterDate
from cdh_slt a
where a.RegisterDate between 03/1992 and 12/1994
order by a.RegisterDate

so let me know the details asap....

thanks

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-04-02 : 07:00:39

Sekar, I think your journey has hit a muddy patch.


DECLARE @fromDate char(7), @toDate char(7)
SET @fromDate = '03/1992'
SET @toDate = '04/1994'

SELECT a.site_id, a.RegisterDate
FROM cdh_slt a
WHERE a.RegisterDate >= CONVERT(datetime, '01/' + @fromDate, 103)
  AND a.RegisterDate < DATEADD(month, 1, CONVERT(datetime, '01/' + @toDate, 103))
ORDER BY a.RegisterDate

BTW, I couldn't make any sense of "If User selects from date as 03/1999 and to date as 04/2000 i have to selects the records where RegisterDateis between 03/1992 and 04/1994"
How does it get from a 13 month period to a 25 month period starting 7 years previously?

Edited by - Arnold Fribble on 04/02/2003 07:13:45

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2003-04-02 : 07:21:20

OOps.!!!

Without trying I answered.

EDIT: Deleted my wrong Suggestion.

Sekar
~~~~
Success is not a destination that you ever reach. Success is the quality of your journey.

Edited by - samsekar on 04/02/2003 07:38:29

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2003-04-02 : 07:48:39

Journey Continues...

DECLARE @date1 char(7), @date2 char(7)
SET @date1 = '03/1992'
SET @date2 = '12/1994'

DECLARE @fromDate Datetime, @toDate datetime
SET @fromDate = Convert(datetime, '01/'+@date1,103) --First Day of the Month.
SET @toDate = Convert(datetime, '01/'+@date2,103) -- First Day of the Month
SET @toDate = Dateadd(day,-1,Dateadd(month,1,@toDate)) -- Last Day of the Month.

SELECT a.site_id, a.RegisterDate
FROM cdh_slt a
WHERE a.RegisterDate Between @fromdate and @todate
ORDER BY a.RegisterDate

Sekar
~~~~
Success is not a destination that you ever reach. Success is the quality of your journey.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources