Click here to Skip to main content
15,888,521 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have this code...

where p.ProductID == product && ( p.PurchaseDate < DateTime.Now.AddDays(-56) && p.PurchaseDate > DateTime.Now.AddDays(-49))

and I executed the LINQ but I got an error..

I caught the query with profiler and the where clause looked like this..

@p8='2011-02-17 18:43:30.5500000',@p9=688,@p10='2011-02-17 18:43:30.5530000',...

which means DateTime.Now.AddDays were passing milliseconds ..
I only need date!

I would like to use DateAdd function from sql.

how can I do that?

please help....
Posted
Updated 25-Mar-11 13:51pm
v3

Calling DateTime.Now two times, results to different milliseconds. You have to cache the current moment:
C#
DateTime now = DateTime.Now;
DateTime fromDate = new DateTime( now.Year, now.Month, now.Day ).AddDays( -49 );
DateTime untilDate = new DateTime( now.Year, now.Month, now.Day ).AddDays( -56 );
...
where p.ProductID == product && ( p.PurchaseDate < untilDate && p.PurchaseDate > fromDate )
 
Share this answer
 
I do not know why your code is not working (Except see **1 below) but I am confident that it is not because of a malfunction in the AddDays() method, unless your installation has become corrupted.

I have just run some tests to make sure. I am checking for a 'test date' being between two other dates.

I created a simple Form containing:
2 NumericUpDown controls (nudStart and nudEnd) for defining the range of dates to check.
1 DateTimePicker (dateTimePicker1) for defining the 'test date'
1 Button (btnGo) to fire off the test
1 TextBox (txtResult - MultiLine on, WordWrap on) to display the outcome

Here is the code I used:
C#
public partial class DateTimeTestForm : Form
{
    DateTime rangeStart = DateTime.MinValue;
    DateTime rangeEnd = DateTime.MaxValue;

    public DateTimeTestForm()
    {
        InitializeComponent();
    }

    private void ShowResult(bool isValid)
    {
        string resultString = "The test date {0:D} is{1} between {2:D} and {3:D}";

        this.txtResult.Text = string.Format(resultString, this.dateTimePicker1.Value,
            isValid ? "" : " NOT",
            rangeStart,
            rangeEnd);
    }

    private void btnGo_Click(object sender, EventArgs e)
    {
        ShowResult(this.dateTimePicker1.Value > rangeStart && this.dateTimePicker1.Value < rangeEnd);
    }

    private void nudStart_ValueChanged(object sender, EventArgs e)
    {
        this.rangeStart = DateTime.Now.AddDays((double)this.nudStart.Value);
    }

    private void nudEnd_ValueChanged(object sender, EventArgs e)
    {
        this.rangeEnd = DateTime.Now.AddDays((double)this.nudEnd.Value);
    }
}


If you want to try this for yourself you will need to hook up the event handlers, set nudStart to -56 and nudEnd to -49, select a date to test in the DateTimePicker (I did two tests 4th Feb 2011 and 8th Feb 2011) then click btnGo. You will see that AddDays() correctly sets the dates.

**1 I notice that you are checking for the PurchaseDate being outside of your date range. Is this what you intended?
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900