Comparing SQL date without current month

Question

Hi!

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

Answer 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)

Answer 2

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)

Answer 3

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

Answer 4

Hi its simple, 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.

Answer 5

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#);