May 5, 2017 at 7:31 am
Hi Friends,
I am trying to wirte a filter condition in WHERE clause
like
CASE WHEN GETDATE() = 'first of this month' THEN 'DATE column' between 'first day of last month' and 'last day of last month'
ELSE 'DATE column' between 'first of current month' and 'current date' END
But this syntax is wrong since I believe after THEN we are not supposed to have BETWEEN.
I know we can do this through Dynamic T-SQL.
Friends, can you help me if there is any other way to write this ?
Let me know if need more explanantion.
Thanks,
Charmer
May 5, 2017 at 7:40 am
Your Boolean expression needs to go in your WHEN clause, not in your THEN (which returns the value).
For example:CASE WHEN Number BETWEEN 0 AND 9 THEN 'Less than 10'
WHEN Number BETWEEN 10 AND 19 THEN 'Teens'
WHEN Number BETWEEN 20 AND 21 THEN 'Twenties'
ELSE '30 or more' END
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 5, 2017 at 8:01 am
Yeah I knew that syntax was wrong, Thom. That's how my requirement is. Based on condition, I need to select records in between fromdate1 to todate 1 else date range between fromdate2 to todate2.
I am thinking to write dynamic statement but I want to know if there was any other way I could rewrite the logic without dynamic statement.
@SQL = 'Select * from table where '
select case when 1=1 then @SQL + ' date range between fromdate1 to todate1' else
@SQL + 'date range between fromdate2 to todate2' END
Any suggestions would be appreciated.
Thanks,
Charmer
May 5, 2017 at 8:07 am
Ahh, now I see what you want.
Try something like this:WHERE DateColumn >= DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(DAY, -1, GETDATE())),0)
AND DateColumn < CASE WHEN DATEPART(DAY, GETDATE()) = 1 THEN DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()),0) ELSE GETDATE() END
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 5, 2017 at 10:44 am
Charmer - Friday, May 5, 2017 8:01 AMYeah I knew that syntax was wrong, Thom. That's how my requirement is. Based on condition, I need to select records in between fromdate1 to todate 1 else date range between fromdate2 to todate2.
I am thinking to write dynamic statement but I want to know if there was any other way I could rewrite the logic without dynamic statement.
@SQL = 'Select * from table where '
select case when 1=1 then @SQL + ' date range between fromdate1 to todate1' else
@SQL + 'date range between fromdate2 to todate2' ENDAny suggestions would be appreciated.
Why don't you avoid getting guesses and post DDL, sample data and expected results?
May 7, 2017 at 10:27 pm
Sure, Luis... I will post the DDL's in a while.
Thanks,
Charmer
May 7, 2017 at 11:42 pm
Declare @temp table (Id int identity(1,1) primary key, empname varchar(15), datecreated date )
insert into @temp (empname, datecreated)
select 'Austin', cast(GETDATE()-37 as date)
union
select 'Chris', cast(GETDATE()-34 as date)
union
select 'Martin', cast(GETDATE()-30 as date)
union
select 'Jeff', cast(GETDATE()-25 as date)
union
select 'Greg', cast(GETDATE()-20 as date)
union
select 'Matt', cast(GETDATE()-15 as date)
union
select 'Ann', cast(GETDATE()-4 as date)
union
select 'Mark', cast(GETDATE()-3 as date)
union
select 'Paula', cast(GETDATE()-1 as date)
union
select 'Presper', cast(GETDATE()-6 as date)
select * from @temp
where
((datecreated between
convert(datetime,(dateadd(MM,-1,cast(month(getdate()) as varchar(4)) + '/1/' + cast(year(getdate()) as varchar(4)))))
and convert(datetime,cast(month(getdate()) as varchar(4)) + '/1/' + cast(year(getdate()) as varchar(4)))-1)
and cast(getdate() as date) = convert(date,cast(month(getdate()) as varchar(4)) + '/1/' + cast(year(getdate()) as varchar(4))))
OR
((datecreated between convert(datetime,cast(month(getdate()) as varchar(4)) + '/1/' + cast(year(getdate()) as varchar(4)))
and convert(datetime,convert(date,getdate())) )
and (cast(getdate() as date)<> convert(date,cast(month(getdate()) as varchar(4)) + '/1/' + cast(year(getdate()) as varchar(4)))))
Finally, I found out the WHERE clause .
Thanks,
Charmer
May 8, 2017 at 12:20 am
You can make it simpler by declaring variables to hold the date valuesDECLARE @Date DATE
DECLARE @StartDate DATE
DECLARE @EndDate DATE
SET @Date = GETDATE()
SET @StartDate =
CASE
WHEN DATEPART( DAY, @Date ) = 1 THEN DATEADD( MONTH, -1, @Date )
ELSE DATEADD( MONTH, DATEDIFF( MONTH, 0, @Date ), 0 )
END
SET @EndDate = DATEADD( DAY, -1, DATEADD( MONTH, 1, @StartDate ) )
SELECT *
FROM @temp
WHERE datecreated BETWEEN @StartDate AND @EndDate
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 8, 2017 at 5:51 am
Even simpler. You're basically trying to query the current month for the day before.
DECLARE @StartDate date = DATEADD(mm, DATEDIFF( mm, 0, GETDATE() - 1), 0);
DECLARE @EndDate date = DATEADD( DAY, -1, DATEADD( MONTH, 1, @StartDate ) );
SELECT *
FROM @temp
WHERE datecreated >= @StartDate
AND datecreated <= @EndDate;
Try to understand the formulas used to calculate the dates. Remember that 0 is equal to date 1900-01-01.
May 9, 2017 at 3:48 am
Thom A - Friday, May 5, 2017 8:07 AMAhh, now I see what you want.Try something like this:
WHERE DateColumn >= DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(DAY, -1, GETDATE())),0)
AND DateColumn < CASE WHEN DATEPART(DAY, GETDATE()) = 1 THEN DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()),0) ELSE GETDATE() END
Thank you, Thom. This works way better than mine.
Thanks,
Charmer
May 9, 2017 at 3:56 am
Luis Cazares - Monday, May 8, 2017 5:51 AMEven simpler. You're basically trying to query the current month for the day before.
DECLARE @StartDate date = DATEADD(mm, DATEDIFF( mm, 0, GETDATE() - 1), 0);
DECLARE @EndDate date = DATEADD( DAY, -1, DATEADD( MONTH, 1, @StartDate ) );SELECT *
FROM @temp
WHERE datecreated >= @StartDate
AND datecreated <= @EndDate;
Try to understand the formulas used to calculate the dates. Remember that 0 is equal to date 1900-01-01.
Hi Luis,
Not only the current month. Actually, I want to query for the last month as well in case if getdate() is first day of current month. Or else query for the current month data.
Thanks,
Charmer
May 9, 2017 at 4:04 am
Charmer - Tuesday, May 9, 2017 3:56 AMLuis Cazares - Monday, May 8, 2017 5:51 AMEven simpler. You're basically trying to query the current month for the day before.
DECLARE @StartDate date = DATEADD(mm, DATEDIFF( mm, 0, GETDATE() - 1), 0);
DECLARE @EndDate date = DATEADD( DAY, -1, DATEADD( MONTH, 1, @StartDate ) );SELECT *
FROM @temp
WHERE datecreated >= @StartDate
AND datecreated <= @EndDate;
Try to understand the formulas used to calculate the dates. Remember that 0 is equal to date 1900-01-01.Hi Luis,
Not only the current month. Actually, I want to query for the last month as well in case if getdate() is first day of current month. Or else query for the current month data.
The query that Luis gave should work for both cases.
Look carefully at the @StartDate declaration, the value changes if GETDATE() returns the first day of the month.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 9, 2017 at 5:50 am
Charmer - Tuesday, May 9, 2017 3:56 AMLuis Cazares - Monday, May 8, 2017 5:51 AMEven simpler. You're basically trying to query the current month for the day before.
DECLARE @StartDate date = DATEADD(mm, DATEDIFF( mm, 0, GETDATE() - 1), 0);
DECLARE @EndDate date = DATEADD( DAY, -1, DATEADD( MONTH, 1, @StartDate ) );SELECT *
FROM @temp
WHERE datecreated >= @StartDate
AND datecreated <= @EndDate;
Try to understand the formulas used to calculate the dates. Remember that 0 is equal to date 1900-01-01.Hi Luis,
Not only the current month. Actually, I want to query for the last month as well in case if getdate() is first day of current month. Or else query for the current month data.
That's why you want the current month for "yesterday". If today is the first day of the month, yesterday is the last from previous month. In all other cases, today and yesterday belong to the same month.
This logic allows you to avoid the CASE expression.
May 10, 2017 at 11:18 am
Luis Cazares - Tuesday, May 9, 2017 5:50 AMCharmer - Tuesday, May 9, 2017 3:56 AMLuis Cazares - Monday, May 8, 2017 5:51 AMEven simpler. You're basically trying to query the current month for the day before.
DECLARE @StartDate date = DATEADD(mm, DATEDIFF( mm, 0, GETDATE() - 1), 0);
DECLARE @EndDate date = DATEADD( DAY, -1, DATEADD( MONTH, 1, @StartDate ) );SELECT *
FROM @temp
WHERE datecreated >= @StartDate
AND datecreated <= @EndDate;
Try to understand the formulas used to calculate the dates. Remember that 0 is equal to date 1900-01-01.Hi Luis,
Not only the current month. Actually, I want to query for the last month as well in case if getdate() is first day of current month. Or else query for the current month data.
That's why you want the current month for "yesterday". If today is the first day of the month, yesterday is the last from previous month. In all other cases, today and yesterday belong to the same month.
This logic allows you to avoid the CASE expression.
Luis, I could not understand. If you don't mind, Could you please explain me a little bit more?
Thanks,
Charmer
May 10, 2017 at 11:59 am
Charmer - Wednesday, May 10, 2017 11:18 AM
Luis, I could not understand. If you don't mind, Could you please explain me a little bit more?
Let me try to give an example with sample data.
On the following query:
- The column Today represents the day you would run the report.
- The column Yesterday means the day before the report is run.
- The columns StartDate and EndDate represent the end and start of the month used for your report.
As you can see, all the dates for today return the current month except for the first day of each month. The first day of each month, it gets the previous month. But if you compare it to the column esterday, the month is always the same for the 3 columns.
WITH E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
cteCalendar(calDate) AS(
SELECT DATEADD( dd, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1, '2017') calDate
FROM E a, E b
)
SELECT
Today = calDate,
Yesterday = calDate - 1,
StartDate = DATEADD(mm, DATEDIFF( mm, 0, calDate - 1), 0),
EndDate = DATEADD( DAY, -1, DATEADD( MONTH, 1, StartDate ) )
FROM cteCalendar
CROSS APPLY (SELECT DATEADD(mm, DATEDIFF( mm, 0, calDate - 1), 0) AS StartDate)x;
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply