how do I get data between two dates

Question

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);
}

Did you try the online documentation? [DateTime.AddYears](http://msdn.microsoft.com/en-us/library/system.datetime.addyears(v=vs.110).aspx) — MattC, Feb 14 '14 at 09:55
Possibly you can find out the relevant answer here : http://stackoverflow.com/questions/2688791/how-to-check-if-a-datetime-range-is-within-another-3-month-datetime-range — Nikhil Joshi, Feb 14 '14 at 09:58

Jon Skeet · Accepted Answer · 2014-02-14T10:13:00.730

5

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);

edited Feb 14 '14 at 10:13

answered Feb 14 '14 at 09:58

Jon Skeet

1,421,763
867
9,128
9,194

@Jon Skeet To understand what you have done i have one question - you have included 1's is that's the months. So if i am looking for the data between December do i write it as `DateTime thisYearStart = new DateTime(thisYear, 12, 12); DateTime nextYearStart = new DateTime(thisYear + 12, 12, 12);` ? – user911 Feb 14 '14 at 10:09
@user911: It depends on what "BETWEEN" does - whether that's inclusive or exclusive. If it's exclusive, you're fine already. If it's inclusive, just use `.AddTicks(-1)` to get the very last tick of the previous year. Will edit the answer. – Jon Skeet Feb 14 '14 at 10:11
Makes seance, Thank you. – user911 Feb 14 '14 at 10:48
Is this method requires formatting date to yyyy/mm/dd because currently i am getting date as mm/dd/yyyy - my query is `WITH cte AS (SELECT ROW_NUMBER() OVER(ORDER BY ID,fname, sname) AS ROW,* FROM TableName WHERE Time > @Time and Time <@Time1) SELECT * FROM cte WHERE ROW BETWEEN 1 AND 100` - i have 500 records so it loads 1 to 100 and store other 400 else where that gets loaded when user clicks more. – user911 Feb 14 '14 at 13:57
@user911: No, you shouldn't need to convert it to a string at all. You should be able to specify the parameter values as `DateTime`. Avoid string conversions as far as possible. – Jon Skeet Feb 14 '14 at 14:00
@JonSkeet you have answered my question but you answer will create a problem for those who are looking to acquire data between October to next year of October. Currently you answer says `Now.Year` so it gets year as 2014 but it should be between October 2013 to October 2014 – user911 Feb 14 '14 at 14:49
@user911: Well I can only go on the requirements of the question you asked, which was Jan 1st to Jan 1st. If you have different requirements, you'll get a slightly different answer... – Jon Skeet Feb 14 '14 at 14:59
@JonSkeet is it possible to find out how would i go about and solve that problem, this will help me and those who might have similar problem. – user911 Feb 14 '14 at 15:13
@user911: Just use `DateTime.Now` as the "start" point, and keep the `.AddYears(1).AddTicks(-1)` to work out the "end" point. – Jon Skeet Feb 14 '14 at 15:21

score 1 · Answer 2 · answered Feb 14 '14 at 10:26

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);

how do I get data between two dates

2 Answers2