LearnItFirst User Forum

Welcome Guest

Search | New Posts | Members | Log In | Register

SQL Server Forum - LearnItFirst.com » Transact-SQL Programming » DML (SELECT, INSERT, UPDATE, DELETE) Questions » Retrieve Records for certain date stamp

Retrieve Records for certain date stamp

Options

Previous Topic · Next Topic

pavanpuligandla

Posted: Tuesday, September 15, 2009 2:53:18 PM

Rank: Newbie

Joined: 9/15/2009
Posts: 3
Points: 9
Where do you live?: india

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

Scott Whigham

Posted: Tuesday, September 15, 2009 4:19:22 PM

Rank: Super Mod

Joined: 3/20/2006
Posts: 460
Points: 1,002
Where do you live?: Dallas, TX

When you say, "I'm saving my video added date in this format: 23/09/2009 12:20:22", what do you mean? I would have assumed that you have a column that is DATETIME but your comment makes me think otherwise. Is it a DATETIME column or a VARCHAR?

Anyway, this is fairly easy - most of the work is done in your web app. You will:

Create a stored procedure that accepts two params: StartDate and EndDate. Your proc will then have a query that retrieves everything found between the two - i.e. WHERE MyDate >= @StartDate AND MyDate <= @EndDate. You just have to watch out for how SQL handles the time portion so that you don't lose data!
In your app, you'll write the code to assign the @StartDate and @EndDate appropriately.

I really think this is more of a front end question and not so much of a SQL question.

pavanpuligandla

Posted: Wednesday, September 16, 2009 1:05:46 AM

Rank: Newbie

Joined: 9/15/2009
Posts: 3
Points: 9
Where do you live?: india

Scott Whigham wrote:

Hi scott,
Thanks for the comeback.
No i'm saving my video added date as DATETIME only. and i've only ONE DATETIME column in my table.
From the front end site ,i'll be passing the MONTH and YEAR as parameter like AUGUST 2009 as STRING type, Now i need to compare the above passed STRING type with my DATETIME columns and show user all AUGUST 2009 results only.
Thats the question, How can i do this? How to convert the MONTH to inte=ger type and match with this date : 22/09/2009 12:20:22 ?/
Hope this is clear to you,
No my question is purely related to Backend only.

pavanpuligandla

Posted: Wednesday, September 16, 2009 4:48:36 AM

Rank: Newbie

Joined: 9/15/2009
Posts: 3
Points: 9
Where do you live?: india

Hi Scott,

After having trials, i got this query. but now i'm getting a conversion datetime from string error.
I'm passing Month and Year as a string parameter to sql server Stored Procedure and trying to pull out SEPTEMBER month 2009 YEAR videos from the database,

Code:

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;

can you please help me out!
ERROR : Msg 241, Level 16, State 1, Line 5
Conversion failed when converting datetime from character string.

Many Thnaks,

Scott Whigham

Posted: Wednesday, September 16, 2009 6:22:57 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 460
Points: 1,002
Where do you live?: Dallas, TX

Look - if you want to do it in SQL, you can (of course) but I'll go ahead and tell you now that I think that's a mistake. It makes no sense IMO to do such a thing in SQL yet it makes total sense to do such a thing in the front end.

Here is just one reason: today you need a search query for a "By Month range" so you write this query that accepts an input like "Sep 2009" and, internally, you convert this to the proper DATETIME format. Will there ever be a time where someone wants to receive the same columns/result set but instead of "By Month", they want "By Week" or "By Day"? If the answer is "Yes" or "Maybe" then you will have to duplicate your query/logic but write new logic to figure out which week or day. In other words, you'd have three stored procedures that all do the same thing but, because they accept input like "Sep 2009" or "Week 54", you have this goofy logic that translates that into a StartDate and EndDate.

If, however, you do it the way I suggested, you have a single stored procedure and it's the front end that decides StartDate and EndDate values. It doesn't matter if it's "By Year", "By Month", etc - the front end passes in the first date and the last date; the query simply returns records within that range. In other words: having a single stored proc is flexible - it can handle many different situations - but your technique is rigid in that it only allows one situation (passing in "Sep 2009" Wink

.

Anyway - if you want to do it, you'll need a CASE operator to handle the translation from the month abbreviate to the actual date value.

Code:

DECLARE @the_year INT, @first_day_of_month DATETIME
SET @the_year = RIGHT(@input_value, 4)

SELECT @first_day_of_month = CAST(CASE
WHEN 'Jan' THEN 'January 1'
WHEN 'Feb' THEN 'February 1'
...
END
+ ' , ' + CAST(@the_year AS CHAR(4)) + '''' AS DATETIME)

DECLARE @last_day_of_month = DATEADD(month, @first_day_of_month, 1) - 1 -- subtract one day

-- You still have to get the last second/millisecond of the last day

That's at least close - I haven't tested it though.

Again let me say that I think this is a terrible way to do it.

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.