SQL Last 6 Months

Question

I have table containing one datetime column  I need to return rows for only last 6 months  This can be done by   where datetime column  gt  DATEADD m  -6  current timestamp    But how to extend this option if I want to return latest month beginning with first day of the month  E g  I run this condition in the middle of month  14 6 2000   the latest row is set to 14 1 2000  but i would like to return it as 1 1 2000  Any advice   I tried some subqueries  max function of datetime including month function  but with no success

User · Answer

select   from tbl1 where datetime column  gt    DATEADD m  -6  convert date  convert varchar 6   getdate   112     01

User · Answer

In MySQL where datetime column  gt  curdate   - interval  dayofmonth curdate    - 1  day - interval 6 month  SQLFiddle demo In SQL Server where datetime column  gt  dateadd m  -6  getdate   - datepart d  getdate      1   SQLFiddle demo

User · Answer

For MS SQL Server  you can use   where datetime column  gt   Dateadd Month  Datediff Month  0  DATEADD m  -6  current timestamp    0

User · Answer

Try this one where datediff month  datetime column  getdate     lt   6 To exclude or filter out future dates where datediff month  datetime column  getdate    between 0 and 6  This part datediff month  datetime column  getdate    will get the month difference in number of current date and Datetime Column and will return Rows like   Result 1  2 3 4 5 6 7 8 9 10  This is Our final condition to get last 6 months data  where result  lt   6

User · Answer

where yourdate column  gt  DATE SUB now    INTERVAL 6 MONTH

[sql] SQL Last 6 Months

Examples related to sql

Examples related to sql-server