Query 'where' between Sysdate and first day of month RSS

5 replies

Last post Mar 26, 2010 07:18 AM by ksridharbabuus

Member

488 Points

720 Posts

Query 'where' between Sysdate and first day of month

Do any of you guys know of a simple where clause to return values between the current SYSDATE and the first day of the month? Like...

SELECT * FROM TABLE WHERE DATE_FIELD BETWEEN SYSDATE AND 'FIRST DAY OF THE MONTH'

"Hey, how come pirates are so cool?"

"They just ARRRGGGHHHH!!!!!"
• Moved by ndinakar on Mar 23, 2010 03:09 PM

All-Star

49092 Points

6868 Posts

Moderator

MVP

Re: Query 'where' between Sysdate and first day of month

First day of the month will always be 1. So you can just get the month and year from Getdate and append to 1 to create a date.

Declare @Firstday Datetime

Select @Firstday = Convert(Datetime, '1/' + ''' + Month(Getdate() + ''/''' + Year(Getdate()) + ''')

SELECT * FROM Table WHERE DatE_Field BETWEEN GetDate() AND  @FirstDay

***********************
Dinakar Nethi
Life is short. Enjoy it.
***********************

Member

488 Points

720 Posts

Re: Query 'where' between Sysdate and first day of month

ndinakar

First day of the month will always be 1. So you can just get the month and year from Getdate and append to 1 to create a date.

Declare @Firstday Datetime

Select @Firstday = Convert(Datetime, '1/' + ''' + Month(Getdate() + ''/''' + Year(Getdate()) + ''')

SELECT * FROM Table WHERE DatE_Field BETWEEN GetDate() AND  @FirstDay

Is there any way I could incorporate this into a sqldatasource within aspx:

```<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%\$ ConnectionStrings:ESO_IntConnect %>" SelectCommand="select qty from req_log
where req_date.....">```
`</asp:SqlDataSource>`

"Hey, how come pirates are so cool?"

"They just ARRRGGGHHHH!!!!!"

Member

488 Points

720 Posts

Re: Query 'where' between Sysdate and first day of month

I'm messing around with the following.  Could I possibly make this to work.  I would need for the default value to be the first day of the month.

```<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%\$ ConnectionStrings:ESO_IntranetConnectionString %>" SelectCommand="select qty from req_log
where (propnum = 'L-0147' and req_date > @reqdate">
<SelectParameters>
<asp:Parameter Name="reqdate" Type="DateTime" DefaultValue=????/>
</SelectParameters>```

"Hey, how come pirates are so cool?"

"They just ARRRGGGHHHH!!!!!"
• Edited by fralo on Mar 23, 2010 08:07 PM

All-Star

33132 Points

2465 Posts

Re: Query 'where' between Sysdate and first day of month

Hi Fralo,

```<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%\$ ConnectionStrings:NorthwindConnectionString %>"
SelectCommand="SELECT [OrderID], [OrderDate], [RequiredDate], [ShippedDate] FROM [Orders] WHERE [OrderDate] >= DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0) AND [OrderDate] < GETDATE()">
</asp:SqlDataSource>```

Jian Kang
Microsoft Online Community Support

Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Star

9239 Points

1356 Posts