June 18, 2014 at 5:20 am
Hi,
as part of a select statement I'm wanting to grab a date range, in effect something like this
AND t.TransactionDate >= '2014-05-01' AND t.TransactionDate <= '2014-05-31 23:59.00'
However, the 2 date's are being passed to the sql as one variable AND in this format 'May 2014'
So is it possible to format the date so sql server will recognize it and also so that one is at the 1st of that month and the other the last day of the month (and time is at the end of the day rather than the start 00:00.00 or maybe have it as the 1st of the following month but at 00:00.00 hours)
thanks,
June 18, 2014 at 5:48 am
actually this does the trick surprisingly...
AND t.TransactionDate >= 'Apr 2014' AND t.TransactionDate <= DATEADD(mm,1,'Apr 2014')
BUT if multiple dates were passed through, say Apr 2014, May 2014, June 2014 so I would want the date range to be from start of Apr 2014 though to end of June, any ideas on how to do that?
thanks
June 18, 2014 at 5:50 am
How would multiple dates be passed?
What happens if the multiple dates aren't in chronological order? (1 May 2014, 2 December 2013, 16 March 2014)?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 18, 2014 at 6:05 am
If you're able to have multiple dates passed to the procedure, you're receiving them as varchars. Here's an approach to handle multiple dates being that aren't necessarily in chronological order:
declare @strPassed varchar(200) = '2014/05/01,Dec 2014,16 March 2014';
with split_dates as (
select CONVERT(datetime, s.item) date_used
from DelimitedSplit8K(@strPassed, ',') s),
date_range as (
select MIN(date_used) start_date, MAX(date_used) end_date
from split_dates)
select start_date, end_date
from date_range;
In the SELECT, you could query your table or assign the dates to variables if you need the min and max dates elsewhere in your procedure. This makes use of Jeff Moden's DelimitedSplit8K to extract the dates from the string. If you don't know it yet, please see the article in my signature - it's well worth the read and will change the way you look at strings. You can also use whatever delimiter you want. I used a comma here, but you could use the pipe or any other single character.
Edit: If you prefer, you could also extract the values in a single query without using a CTE at all:
declare @strPassed varchar(200) = '2014/05/01,Dec 2014,16 March 2014';
select MIN(convert(datetime, s.item)), MAX(convert(datetime, s.item))
from DelimitedSplit8K(@strPassed, ',') s;
Is this what you're looking for?
June 18, 2014 at 7:01 am
Just in answer to GilaMonster, the dates/multiple dates passed are always going to be just the month and year and I'm pretty sure there will always be in month order. So no actual day of months passed in.
I'll just have a look at the other post now too.
June 18, 2014 at 2:20 pm
Minor correction:
t.TransactionDate >= 'Apr 2014' AND
t.TransactionDate < DATEADD(mm,1,'Apr 2014') --"<" rather than "<="
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 19, 2014 at 3:42 am
Oh yes, thanks Scott
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply