July 22, 2014 at 5:28 am
Hello,
I have no idea why i can't get this to work.
In my where clause, i have a datetime field which I am specifying to be less that the previous month. For what ever reason it just doesn't work! even if I use just plain getdate()
so my select statement , date is the datetime column form the source table.
SELECT [DATE], getdate() as today, (DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))) as lastdayofpreviousperiod_getdate
from table
where [DATE] <= (DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))
the result you can see it clearly ignores the rule!
DATE getdate firstofprevousperiod_getdate rubbish
2014-06-30 00:00:00.0002014-07-22 12:20:36.7702014-06-30 23:59:59.000
2014-07-01 00:00:00.0002014-07-22 12:20:36.7702014-06-30 23:59:59.000200500
2014-07-01 00:00:00.0002014-07-22 12:20:36.7702014-06-30 23:59:59.000200500
2014-07-01 00:00:00.0002014-07-22 12:20:36.7702014-06-30 23:59:59.000200100
2014-07-01 00:00:00.0002014-07-22 12:20:36.7702014-06-30 23:59:59.000200300
2014-07-01 00:00:00.0002014-07-22 12:20:36.7702014-06-30 23:59:59.000200300
2014-07-02 00:00:00.0002014-07-22 12:20:36.7702014-06-30 23:59:59.000200500
2014-07-02 00:00:00.0002014-07-22 12:20:36.7702014-06-30 23:59:59.000200500
2014-07-02 00:00:00.0002014-07-22 12:20:36.7702014-06-30 23:59:59.000200500
2014-07-02 00:00:00.0002014-07-22 12:20:36.7702014-06-30 23:59:59.000200500
2014-07-02 00:00:00.0002014-07-22 12:20:36.7702014-06-30 23:59:59.000200100
2014-07-02 00:00:00.0002014-07-22 12:20:36.7702014-06-30 23:59:59.000200300
2014-07-03 00:00:00.0002014-07-22 12:20:36.7702014-06-30 23:59:59.000200500
2014-07-03 00:00:00.0002014-07-22 12:20:36.7702014-06-30 23:59:59.000200100
2014-07-03 00:00:00.0002014-07-22 12:20:36.7702014-06-30 23:59:59.000200300
2014-07-04 00:00:00.0002014-07-22 12:20:36.7702014-06-30 23:59:59.000200500
July 22, 2014 at 5:30 am
Table definition and easily usable sample data please (set of insert statements)
Exactly what is the query supposed to do?
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
July 22, 2014 at 5:31 am
it is supposed to return rows which have dates less than 30th of June
July 22, 2014 at 5:37 am
1 out of three...
What's wrong then with just
WHERE [DATE] < '2014/06/30'
if all it's supposed to do is return rows with a date before the 30th June this year?
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
July 22, 2014 at 6:08 am
the query needs to be dynamic so I don't want to hard code anything. The data will be pulled into month end reporting.
The idea of it is to return all rows which are upto the last working day of the previous month.
I have solved the issue, In my where clasue I had a range of dtae which was not wrapped, so wrapping that stament made work.
(
(NOMINAL_CODE between '200000' and '209999')
or
(NOMINAL_CODE between '210000' and '210799')
)
July 22, 2014 at 6:26 am
Missing brackets around an OR? That will do it, AND has precedence over OR, so anytime you have OR and AND, you need to use brackets to ensure that the order of operation is what you intend.
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
July 22, 2014 at 11:08 am
Btw, for date/datetime, don't use "<=" on the end date, use "<".
In this case, instead of:
[DATE] <= (DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))
do this:
[DATE] < (DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 14, 2014 at 12:59 pm
Or if you want less than the last day of the prior month (instead of less than the first day of the month)... just subtract a day from the answer Scott gave you -
SELECT
dateadd(d, -1,
(DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) )
As in [Date] < dateadd(d, -1,(DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) )
----------------------------------------------------
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply