Return Records for certain date stamp

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)

Return Records for certain date stamp

Author

Topic

maruthi_p
Starting Member

8 Posts

Posted - 2009-09-15 : 15:50:12

Hi all,
i'm storing video files on my SQL Server 2005 Db and displaying all of them in .aspx page on the front end site.
My requirement is, i need to provide the search functionality with which an user can select MONTH and YEAR from the calendar control and submits, i need to show the results based on MONTH and YEAR.

Example:

If i select "SEPTEMBER 2009" as INPUT, i need to retrieve records with dates from 01/09/2009 - 30/09/2009 and display them on the front end site.
Note : I'm saving my video added date in this format: 23/09/2009 12:20:22

I;m confused, how to do this? i thought of passing the front end parameter as a single string and splitting it into AUGUST and 2009 then comparing with the DATETIME format of VIDEO ADDED DATE column.

can anyone please help me out..
Many Thanks,
Pavan

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-15 : 16:46:11

assuming your date is a datetime datatype:

where year(<dateColumn>) = @year --ie: 2009
and datename(month, <dateColumn>) = @month --ie: September

Be One with the Optimizer
TG

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-15 : 21:32:47

[code]
where datecol >= dateadd(month, @month - 1, dateadd(year, @year - 1900, 0)),
and datecol < dateadd(month, @month, dateadd(year, @year - 1900, 0))
[/code]

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-16 : 02:32:21

If your input is 'SEPTEMBER 2009',

where
datecol>= cast(@month_year as datetime) and
datecol< dateadd(month,1,cast(@month_year as datetime))

Madhivanan

Failing to plan is Planning to fail

maruthi_p
Starting Member

8 Posts

Posted - 2009-09-16 : 05:55:43

quote:
Originally posted by madhivanan

If your input is 'SEPTEMBER 2009',

where
datecol>= cast(@month_year as datetime) and
datecol< dateadd(month,1,cast(@month_year as datetime))

Madhivanan

Failing to plan is Planning to fail

Hi,
Thanks for the reply,
But i'm getting a syntax error at >, My Query is like this:

Declare @dt_search AS varchar;
    set @dt_search='September 2009';
    select * from videos_master video_uploaded_date>= cast(@dt_search as datetime) and video_uploaded_date< dateadd(month,1,cast(@dt_search as datetime))

can i use this query to retrieve all September month's videos from the database?

Declare @start_dt AS varchar;

set @start_dt = 'Sep 2009';

SELECT *
FROM videos_master
where substring(CONVERT(varchar,video_uploaded_date,102),1,7)=substring(CONVERT(varchar,CONVERT(datetime,'01 '+@start_dt,113),102),1,7)
ORDER BY video_uploaded_date DESC;

Many Thanks for the comeback,
Pavan.P

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-16 : 06:29:03

Declare @dt_search AS varchar;
set @dt_search='September 2009';
select * from videos_master where video_uploaded_date>= cast(@dt_search as datetime) and video_uploaded_date< dateadd(month,1,cast(@dt_search as datetime))

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources