Link to home
Create AccountLog in
Avatar of Petermcg001
Petermcg001Flag for United Kingdom of Great Britain and Northern Ireland

asked on

SQL statement for dates on or after first day of current month

Hello,

I'm writing a command to extract all records from the table "Orders" for all records where the column "OrderDate" is within the current month.  The OrderDate column is defined as a smalldatetime.

Please could anyone help with the format of the command.

Thanks
ASKER CERTIFIED SOLUTION
Avatar of Barry Cunney
Barry Cunney
Flag of Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Lee
select * from orders where datepart(m, orderdate) = datepart(m, getdate()) and datepart(yyyy, orderdate) = datepart(yyyy, getdate())

Not tested but it should work fine.
Avatar of Petermcg001

ASKER

Thanks to both for looking at this.

Works fine.
Avatar of bmwflyer
bmwflyer

if in a proc you could do this...

declare @currentdate date, @startdate date, @enddate date
set @currentdate = getdate()
set @startdate = cast(cast(month(@currentdate) as nvarchar(2)) + '/1/' + cast(year(@currentdate) as nvarchar(4)) as date)
set @endDate = dateadd(d, -1, dateadd(m, 1, @startdate))

select * from Orders where OrderDate between @startdate and @enddate


if you do this in-line you will need to replace @startdate and @enddate like

select * from Orders where OrderDate between cast(cast(month(@getdate()) as nvarchar(2)) + '/1/' + cast(year(@getdate()) as nvarchar(4)) as date) and dateadd(d, -1, dateadd(m, 1, cast(cast(month(@getdate()) as nvarchar(2)) + '/1/' + cast(year(@getdate()) as nvarchar(4)) as date)))

There may be more elegant ways to do it, but this works.