2

I have a table in Access with string columns and a date column. I want to get all the rows from the table when the date is lower than 22.10.2010, except this month. So, i need the rows from 30.09.2010 to ...

I tied something, but I figured out it's not right: 

SELECT name FROM table WHERE YEAR(date)<=2010 AND MONTH(date)<10

But this solution it's not good, and i have no other idea. Could you help me with a general solution, pls? Thanks

Brad
  • 15,361
  • 6
  • 36
  • 57
DaJackal
  • 2,085
  • 4
  • 32
  • 48

6 Answers6

1

Access?

String together the year, month, and "1" (to get the first day of the month of date) and convert that to a Date.

SELECT  *
FROM    MyTable
WHERE   dateColumn 
           < CDate(CStr(YEAR(date)) + "-" + CStr(MONTH(date)) + "-1")

SQL

Subtract the DAY (minus one) from the date in question from the date to get the first of the month. Then return all rows less than this value.

DECLARE @date DATE
SET @date = GETDATE()DECLARE

SELECT  *
FROM    MyTable
WHERE   dateColumn 
           < DATEADD(DAY, -( DATEPART(DAY, @date) - 1 ), @date)
Brad
  • 15,361
  • 6
  • 36
  • 57
  • I'm not in sql server, Iso I tried this: SELECT * FROM MyTable WHERE dateColumn < (DATEADD(DAY(d), -( DATEPART(DAY(d), d) - 1 ), d)), where d is 22-10-2010, and it's telling me: "This expression is typed incorrectly, or is too complex to be evaluated" – DaJackal Oct 22 '10 at 13:09
  • @DaJackal, this may not be of much help since I don't have Access readily available, but see if you can get it to work for you. – Brad Oct 22 '10 at 13:20
1

The zeroth day of a month is the last day of the previous month:

DateSerial(Year(Date()),Month(Date()),0)

Therefore:

 SELECT [name] FROM [table] 
 WHERE [date]<=DateSerial(Year(Date()),Month(Date()),0)
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • This is a very good solution, and elegant too, thank you very much, it's working, but I have to try mangokun's solution too, it seems stunningly simple ad I think it could work too. Thanks again! – DaJackal Oct 22 '10 at 13:17
  • 1
    Just bear in mind that mangokun's solution is not sargable: http://en.wikipedia.org/wiki/Sargable – Fionnuala Oct 22 '10 at 13:21
1

SELECT name FROM table WHERE ( YEAR(date)<2010 ) OR ( YEAR(date)=2010 AND MONTH(date)<10 )

mangokun
  • 5,493
  • 2
  • 15
  • 8
0

The below functions can be used. These even work for leap years.

'If you pass #2016/01/20# you get #2016/01/31#

Public Function GetLastDate(tempDate As Date) As Date
    GetLastDate = DateSerial(Year(tempDate), Month(tempDate) + 1, 0)
End Function

'If you pass #2016/01/20# you get 31

Public Function GetLastDay(tempDate As Date) As Integer
    GetLastDay = Day(DateSerial(Year(tempDate), Month(tempDate) + 1, 0))
End Function
Adarsh Madrecha
  • 6,364
  • 11
  • 69
  • 117
0

you subtract the number of days in the month so far and use that date for the comparison

Select myName FROM myTable Where myTable.myDate <=DateAdd("d",-Day(Now()),Now());

The above query will give you all the records till the end of the last day of the last month.

Cheers.

  • Is there some affirmative reason why you're using Now() instead of Date()? Nobody mentioned anything other than dates, and Now() returns a date and time component, and won't necessarily give you the right results. – David-W-Fenton Oct 23 '10 at 01:18
0
SELECT you_col
  FROM YourTable
 WHERE your_date 
          < DATEADD('M', 
              DATEDIFF('M', #1990-01-01 00:00:00#, NOW()
          ), #1990-01-01 00:00:00#);
onedaywhen
  • 55,269
  • 12
  • 100
  • 138