March 18, 2011 at 2:22 am
I have a query:
SELECT * FROM sports_program where tf_genre_desc138 like '%basketball%' and tf_genre_desc137='Sports Event' order by tf_reduced_titlec
I have also a field 'tf_org_air_date' that contains a string like 20110305 which means March 05, 2011. I want my query to pull the said data for only 20110304, 20110305 & 20110306. So the query above is great but I want to extend it so I have only the records in my timeframe. It will get me from 1000 records to about 40 records or so for the query. I am wrestling with it and have not been able to get it working to my satisfaction yet. Any ideas? Many thanks in advance.
March 18, 2011 at 2:52 am
you can try:
SELECT [column_names_here_instead_of *] FROM sports_program
where tf_org_air_date between ((dateadd(second,[20110304],'1 Jan 1970 02:00'))
AND
((dateadd(second,[20110306],'1 Jan 1970 02:00'))
AND
tf_genre_desc138 like '%basketball%'
and tf_genre_desc137='Sports Event'
order by tf_reduced_titlec
might need some tweaking on the dateparts.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
March 18, 2011 at 3:37 am
Try this
SELECT * FROM sports_program where tf_genre_desc138 like '%basketball%'
and tf_genre_desc137='Sports Event'
and convert(datetime,tf_org_air_date) between cast('03/05/2011' as date time) and cast('03/07/2011' as datetime)
order by tf_reduced_titlec
March 18, 2011 at 4:06 am
Eric, Rahuzz above is on the money for your solution here. Let us know if you run into problems with that solution. If you do, please post the DDL for the table this data is coming from and a few sample rows from the table (xxxxxx out anything confidential, but don't modify the datestring you're using) if that doesn't work.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 18, 2011 at 8:47 am
Craig Farrell (3/18/2011)
Eric, Rahuzz above is on the money for your solution here. Let us know if you run into problems with that solution. If you do, please post the DDL for the table this data is coming from and a few sample rows from the table (xxxxxx out anything confidential, but don't modify the datestring you're using) if that doesn't work.
Given the date field as a string and fields like tf_genre_desc137 and tf_genre_desc138 I am kind of scared to see the ddl. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 18, 2011 at 12:46 pm
Really great replies. I need to digest all of this material.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply