PNRT
asked on
VB.Net Establish if a date falls between two other dates
I am trying to establish if a date falls between two dates including the start and finish dates
Can someone please explain why this code doesn't work
It works correctly if the StartDate and The EndDate are dates before and after the target date, but if either the StartDate or the EndDate is the same as the TargetDate it fails. It also fails if the dates are all the same.
I need to be able to include both the Start and End Dates and I would have thought that the = sign would have been sufficient.
Many Thanks in advance for any assistance
Can someone please explain why this code doesn't work
If TargetDate >= StartDate AndAlso TargetDate <= EndDate Then
MessageBox.Show("True")
Else
MessageBox.Show("false")
End IfIt works correctly if the StartDate and The EndDate are dates before and after the target date, but if either the StartDate or the EndDate is the same as the TargetDate it fails. It also fails if the dates are all the same.
I need to be able to include both the Start and End Dates and I would have thought that the = sign would have been sufficient.
Many Thanks in advance for any assistance
Mike Tomlinson
A DateTime instance has a Time associated with it. Make sure your start StartDate has 0000 hrs in it and your EndDate has 2359 hrs.
ASKER
Hi, I have declared all variables as Date, would that still have a time element?
No Date has not time in it. try
I think the reason it is not working is possibly the data is bad and doesn't translate to date or is null. That is why I added IsDate(0 to make sure.
If (TargetDate >= StartDate AndAlso TargetDate <= EndDate) And (IsDate(TargetDate) and IsDate(EndDate))Then
MessageBox.Show("True")
Elsehttp://www.experts-exchange.com/Programming/Languages/.NET/Visual_Basic.NET/Q_28612150.html#
MessageBox.Show("false")
End IfI think the reason it is not working is possibly the data is bad and doesn't translate to date or is null. That is why I added IsDate(0 to make sure.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sorry to contradict eghtebas, but the Date type always has a time element. The underlying type in the framework is more properly called a datetime.
If you assign a date without specifying the time, it defaults to midnight.
But too many programmers assign a Date value through the Now function. Now includes the time. So if you use Now at different times in the day, you end up with dates (datetime under the hood) that might seem to be the same when you display them with methods that do now display the time, such as ToShortDateString. But the time is there anyway, and because it is different, = does not work.
When the time component is not important, never use Now, because you end up with your problem. Use Date.Today, which always record the date at midnight, so the time is not significant when you compare two dates.
This is not only in .NET, the same thing was True in VB6. Many databases also record the date and time in a Date field, so you have to be careful about using the right functions when you assign a date where the time is not important.
If you have no control about the way the data is entered, or if you work with old data, you can always use StartDate.Date and EndDate.Date for your comparison. This uses only the date component, not matter what time was recorded.
If you assign a date without specifying the time, it defaults to midnight.
But too many programmers assign a Date value through the Now function. Now includes the time. So if you use Now at different times in the day, you end up with dates (datetime under the hood) that might seem to be the same when you display them with methods that do now display the time, such as ToShortDateString. But the time is there anyway, and because it is different, = does not work.
When the time component is not important, never use Now, because you end up with your problem. Use Date.Today, which always record the date at midnight, so the time is not significant when you compare two dates.
This is not only in .NET, the same thing was True in VB6. Many databases also record the date and time in a Date field, so you have to be careful about using the right functions when you assign a date where the time is not important.
If you have no control about the way the data is entered, or if you work with old data, you can always use StartDate.Date and EndDate.Date for your comparison. This uses only the date component, not matter what time was recorded.
James,
Thank you for the correction. In T-SQL it has no time. Now I am learning from a great teacher like you vb.net is different. see:
https://msdn.microsoft.com/en-us/library/bb630352.aspx
date <-- SQL Server data type:
YYYY-MM-DD <-- Default string literal format passed to down-level client
Is my take correct?
Mike
Thank you for the correction. In T-SQL it has no time. Now I am learning from a great teacher like you vb.net is different. see:
https://msdn.microsoft.com/en-us/library/bb630352.aspx
date <-- SQL Server data type:
YYYY-MM-DD <-- Default string literal format passed to down-level client
Is my take correct?
Mike
egthebas
I am not a "great teacher", I am an "old programmer", who have seen and lived all the changes through the years. And I am not a teacher, I am a technical trainer. Not just words, there are a lot of differences.
You are OK for SQL Server, but the question was about VB.NET. A Date is not a date.
And the date and time fields are a relatively new thing in T-SQL. They appeared in 2008. Before that, we had only a few datetime field types. A lot of database in use today were built before that, or by programmers who did not learn of the new types. So I still see a lot of bad uses of dates.
Access, C and C++, VB6 and COM, .NET and old versions of SQL Server all record the date and time together.
I am not a "great teacher", I am an "old programmer", who have seen and lived all the changes through the years. And I am not a teacher, I am a technical trainer. Not just words, there are a lot of differences.
You are OK for SQL Server, but the question was about VB.NET. A Date is not a date.
And the date and time fields are a relatively new thing in T-SQL. They appeared in 2008. Before that, we had only a few datetime field types. A lot of database in use today were built before that, or by programmers who did not learn of the new types. So I still see a lot of bad uses of dates.
Access, C and C++, VB6 and COM, .NET and old versions of SQL Server all record the date and time together.
ASKER
Hi Experts
All the comments about time always being part of a date may be true, but TargetDate.Date, StartDate.Date and EndDate.Date obviously is
only the date as this seems to work fine. Thanks to all.
All the comments about time always being part of a date may be true, but TargetDate.Date, StartDate.Date and EndDate.Date obviously is
only the date as this seems to work fine. Thanks to all.