Comparing Dates - date format issue

Question

In my web form, I am trying to figure out the number of days off not including the weekend and holidays. I have a SQL database where the holiday date is in date format (yyyy-MM-dd). The user adds dates using the format MM/dd/yyyy. Not counting weekend part of my code works. Figuring out if a date is a holiday, and if it is, not counting it does not work. Here is my code. I've tried everything that is remarked out. I appreciate any help to figure out how to compare the entered date with the SQL date.

Dim intCount As Integer
Dim temp As Integer

'Set same day as 1 <-- your preference. Though you can't work out averages if they are 0.
'Im not checking if this day is a holiday (though in theory you shouldn't have to!).
If StartDate = EndDate Then
    WorkingDays = 1 'Change me to your needs.
    Exit Function
End If

intCount = 0 ' Now we start counting days. 'If you always want to count the first day, set this to 1.

Do Until StartDate = EndDate

    'First, we find out if this day is a weekday or a weekend.
    'If weekday, 1 gets added to the number of days.
    Select Case Weekday(StartDate)
        Case Is = 1, 7
            intCount = intCount 'Weekend, so nothing added.
        Case Else
            intCount = intCount + 1
    End Select

    'Now, if this day was a holiday, we take it back off again!
    'Dim vsqldate = DateTime.ParseExact(StartDate, "yyyy-MM-dd", Nothing)
    'Dim vsqldate = StartDate.ToString("yyyy-MM-dd")
    'Dim vsqldate = fromDt.ToString("yyyy-MM-dd")
    Dim vsqldate = CDate(Format(StartDate, "yyyy-MM-dd"))

    Dim strWhere = "Holiday= #" & vsqldate & "#"  'change fieldname.

    If (DCount("Holiday", "tblCodesholidays", strWhere) > 0) Then 'Change to your field/table names.
        intCount = intCount - 1
    End If

    StartDate = StartDate.AddDays(1) 'We move to the next day.

Loop

WorkingDays = intCount

Fuck StackOverflow · Accepted Answer · 2014-02-28 17:04:58Z

0

From the SQL Server perspective, a date in the format 'yyyymmdd' is considered language neutral. So, try this in your VB.Net code:

Dim vsqldate As DateTime 
vsqldate = DateTime.ParseExact(StartDate.ToString("MM/dd/yyyy"))
Dim strWhere = "Holiday= '" & vsqldate.ToString("yyyyMMdd") & "'"  'change fieldname.

edited Feb 28, 2014 at 17:04

answered Feb 28, 2014 at 16:09

Fuck StackOverflow

5,0462 gold badges26 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user3033348 Over a year ago

How do I define vsqldate? None of the ways I tried above worked. Thanks!

Fuck StackOverflow Over a year ago

Maybe I misunderstood your question. Is vsqldate a date entered on a form by the end user?

user3033348 Over a year ago

StartDate is the name of the field on the form. I defined a variable for StartDate so I could compare it to the date field, Holiday, in the SQL table tblCodesholidays. If StartDate = Holiday (date in table), then I don't want to count it. FYI - I did try to define vsqldate just as StartDate, but that did not work. Thanks for helping me!

Fuck StackOverflow Over a year ago

To grab the date from the form and store it in a variable, something like this should work: Dim vsqldate As DateTime vsqldate = DateTime.ParseExact(StartDate.ToString("MM/dd/yyyy"))

user3033348 Over a year ago

I'm getting an error "Overload resolution failed because no accessible 'ParseExact' accepts this number of arguments" on the vsqldate = line. Just in case you need the function statement, it is: Public Function WorkingDays(ByVal StartDate As Date, ByVal EndDate As Date) As Integer

|

Collectives™ on Stack Overflow

Comparing Dates - date format issue

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related