CODEWITHSUNDEEP
c#sql
user911
user911

Reputation: 35

how do I get data between two dates

I would like to get data between two years, so from 1st of Jan to 1st of Jan, This is what i have done:

public list<a>method(){
    DateTime d= new DateTime(DateTime.Now.Year, 01, 1);
    DateTime dd= new DateTime(DateTime..Year, 01, 1); <- instead 'now' what is the syntax for next year?
    SqlCommand command = new SqlCommand("SELECT TOP 100 ID FROM TableNameWHERE Time Between @Time AND @Time1 ORDER BY Time OFFSET 10 ROWS FETCH NEXT 100 ROWS ONLY", conn);
    command.Parameters.AddWithValue("@Time", d);
    command.Parameters.AddWithValue("@Time1", dd);
}

Upvotes: 0

Views: 212

Answers (2)

Sohail Ali
Sohail Ali

Reputation: 468

I have just modified your code in second date

Your Modified Code:

    DateTime d = new DateTime(DateTime.Now.Year, 01, 1);
    DateTime dd = new DateTime(DateTime.Today.Year + 1 , 01, 1); //Without "Now" you can used today and add +1 which will return the next year integer value
    SqlCommand command = new SqlCommand("SELECT TOP 100 ID FROM TableName WHERE Time Between @Time AND @Time1 ORDER BY Time OFFSET 10 ROWS FETCH NEXT 100 ROWS ONLY", conn);
    command.Parameters.AddWithValue("@Time", d);
    command.Parameters.AddWithValue("@Time1", dd);

or

you can also used this method which return the full date like this "year/mm/dd". you need to add one more statement to get the year.

    DateTime nextYearDate = DateTime.Now.AddYears(1);

now used this date in your DateTime dd.

    DateTime dd = new DateTime(nextYearDate,01,1);

Upvotes: 1

Jon Skeet
Jon Skeet

Reputation: 1504052

If you just want "the next year" it's simple:

// Note: more descriptive variable names than "d" and "dd"
int thisYear = DateTime.Now.Year; // Note this is in the local time zone...
DateTime thisYearStart = new DateTime(thisYear, 1, 1);
DateTime nextYearStart = new DateTime(thisYear + 1, 1, 1);

Or:

int thisYear = DateTime.Now.Year;
DateTime thisYearStart = new DateTime(thisYear, 1, 1);
DateTime nextYearStart = thisYearStart.AddYears(1);

Note the comment about time zones - use UtcNow if you want the "UTC year", in which case you probably want to specify a DateTimeKind of Utc as well.

EDIT: This is assuming you can use an inclusive start time and an exclusive end time. That's generally a nice way of working (because the next inclusive start time ends up being the current exclusive end time, and you don't need to worry about granularity). However, if you want the last tick of the current year for an inclusive upper bound, you can use:

int thisYear = DateTime.Now.Year;
DateTime thisYearStart = new DateTime(thisYear, 1, 1);
DateTime thisYearEnd = thisYearStart.AddYears(1).AddTicks(-1);

Upvotes: 5

Related Questions