ASP.NET

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

5 replies

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

‹ Previous Thread|Next Thread ›

- Reply
fralo

Member

488 Points

720 Posts

Query 'where' between Sysdate and first day of month

Mar 23, 2010 02:13 PM|LINK

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
- Reply
ndinakar

All-Star

49092 Points

6868 Posts

Moderator
MVP

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

Mar 23, 2010 03:20 PM|LINK

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.
***********************
- Reply
fralo

Member

488 Points

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

Mar 23, 2010 03:30 PM|LINK
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!!!!!"
- Reply
fralo

Member

488 Points

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

Mar 23, 2010 08:05 PM|LINK
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
- Reply
Jian Kang - ...

All-Star

33132 Points

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

Mar 26, 2010 06:44 AM|LINK
Hi Fralo,

Please change it as follows:
```
<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.
- Marked as answer by Jian Kang - MSFT on Mar 29, 2010 03:27 AM
- Reply
ksridharbabu...

Star

9239 Points

1356 Posts

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

Mar 26, 2010 07:18 AM|LINK

Hi,

You no need to define any additional parameter to your SqlDataSource as you are expecting filter from 1st of the current month to the current date. You can following where clause:

WHERE [OrderDate] BETWEEN (DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)) AND GETDATE()

-Sri
Visit My Blog
-------------------------------------------------
If this post was useful to you, please mark it as answer. Thank you!