-3

Can someone please show me how to write a T-SQL query to return date range from 1st of current month to forever?

In other words, where all records have a date greater than August 1, 2012.

Thanks!

EDIT: SQL Server 2000 I don't need date range. I just need all dates after the beginning of the current month. But I can't hard code the current month since I won't know it.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
trevoray
  • 317
  • 1
  • 11
  • 28
  • possible duplicate of [How can I select the first day of a month in SQL?](http://stackoverflow.com/questions/1520789/how-can-i-select-the-first-day-of-a-month-in-sql) – LittleBobbyTables - Au Revoir Aug 14 '12 at 18:51
  • 3
    [**What have you tried?**](http://www.whathaveyoutried.com) – marc_s Aug 14 '12 at 18:51
  • What data type would you like `FOREVER` in? – HABO Aug 14 '12 at 19:01
  • I don't want to select the first day of the month. SQL Server 2000. I'm not selecting a date. I'm selecting all records after the first of the current month. – trevoray Aug 14 '12 at 19:23
  • "After the first" or "from the first"? E.g. can you please clarify your intention, do you want data from the first day of the month or not? "After" is ambiguous at best. – Aaron Bertrand Aug 14 '12 at 19:25

2 Answers2

1

I assume you want everything from this month, which is "greater than or equal to" not "greater than." If your wording is accurate then change >= to >:

SELECT x 
  FROM dbo.table_name 
  WHERE DateColumn >= DATEADD(MONTH, 
    DATEDIFF(MONTH, '20000101', CURRENT_TIMESTAMP), '20000101');
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
0

"Where all records have a date greater than August 1, 2012":

select 42 from MyTable where MyDate > Convert( Date, '20120801', 112 )

That should avoid some internationalization issues.

HABO
  • 15,314
  • 5
  • 39
  • 57
  • 1
    Or just use `where Mydate > '20120801' ` - that's safe in **all** versions of SQL Server and with **all** language/regional settings, too (no need to convert that to a `DATE` explicitly - esp. since `DATE` as a datatype was introduced in SQL Server **2008** only and isn't available in e.g. 2005) – marc_s Aug 14 '12 at 19:11
  • The problem with this solution is that you've hard-coded the date. I could be wrong, but I think the programming difficulty experienced here is probably getting August 1st, not constructing the where clause. – Aaron Bertrand Aug 14 '12 at 19:21
  • I can't use this hard coded solution. I need all records with a date after the 1st of the current month, whatever that month may be. I'm using SQL Server 2000. – trevoray Aug 14 '12 at 19:24