kiLLua kiLLua - 1 year ago 98
SQL Question

How to fetch the correct dates

guys sorry I don't really know how to title my question. and not so good in English.

but here's my problem:

I want to fetch the birthdays of employees. from a specified month

eg: For the month of "August" I want to fetch data from dates 14 - 20

I'm having a hard time because each has different birth years.

Here is what i have tried. but doesn't work, i also search the internet but don't have a single clue.

Dim monthName As String = cb_month.Text 'from combobox that has list of months
Dim monthNumber = DateTime.ParseExact(monthName, "MMMM", CultureInfo.CurrentCulture).Month
Dim fromday = CInt(cb_dayfrom.Text)
Dim todate = cb_dayto.Text
' Here's my query
query = "SELECT empID AS 'ID', Name, Cast(Birthday AS DATE) FROM empInfo BETWEEN '%" & monthNumber & "-" & fromday.ToString & "%' AND '%" & monthNumber & "-" & todate & "%'"

and oh, Another one problem is that:

the line Cast(Birthday AS DATE) returns date with Time, how do i get rid of this?

The data type of Birthday in sql is Date

Answer Source

At Last! found an answer to my problem ..

    Dim monthNumber As Integer = DateTime.ParseExact(cb_month.Text, "MMMM", CultureInfo.CurrentCulture).Month
    Dim fromdate As Integer = CInt(cb_dayfrom.Text)
    Dim todate As Integer = CInt(cb_dayto.Text)
    query = "SELECT empID AS 'ID', Name, CONVERT(VARCHAR(10),Birthday,101) FROM empInfo " & _
        "WHERE DATEPART (month, Birthday) = " & monthNumber & _
        " AND (DATEPART (day, Birthday) Between " & fromdate & " AND " & todate & ")"
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download