You must Sign In to post a response.
  • Category: Visual Studio

    VB.NET to retrieve data from sql server using datetime picker as its filter.

    I am writing program to take all data between range of two datetime from sql using my datetimepicker as my variable. My problem is data returns null when i execute my code as below:

    Dim a = DateTimePicker1.Value.ToString("yyyy-MM-dd hh:mm:ss" + "' AM'")
    Dim b = DateTimePicker2.Value.ToString("yyyy-MM-dd hh:mm:ss" + "' PM'")

    Dim sql = "SELECT * FROM atable WHERE Date BETWEEN @Date1 AND @Date2"
    Dim cmd = New SqlCommand(sql, con)
    cmd.Connection = con
    cmd.Parameters.AddWithValue("@Date1", a)
    cmd.Parameters.AddWithValue("@Date2", b)
    Dim myReader As SqlDataReader = cmd.ExecuteReader()
    myreader return value of null. is this date format prob or my reader prob? Thanks
  • #764858
    If you are using dateTimePicker1.Value.ToString("yyyy-MM-dd hh:mm:ss" + "' AM'") then you have to make sure that database column 'Date' must be of type datetime.
    else you can use
    Shashikant Gurav

  • #764861

    Slightly modify your query like below and see if it works,

    Dim sql = "SELECT * FROM atable WHERE Date > @Date1 AND and Date < @Date2"

    Let me know the result after trying this.

    Asheej T K
    Microsoft MVP[ASP.NET/IIS]
    DotNetSpider MVM

  • #764872
    Hi, Shashikant Gurav
    I have already try your syntax but its not working. My date in database is look like this 2008-05-12 12:05:40 PM....If I convert my date time picker like this
    Dim a = DateTimePicker1.Value.ToString("yyyy-MM-dd") it can fetch data but the problem is that if I choose the start date 2008-05-01 to end date 2008-05-03 the data retrieved only from 2008-05-01 to 2008-05-02 it will not fetching data until the end time means that it will be short of one day of data.


  • #764873
    Hi, Asheej T K
    I already try your code but its return null.

  • #764874
    It is coming one short of date because your dates for eg. 2008-05-03 may be looks like this in database 2008-05-03 12:05:40 PM i.e. this date is interpreted as midnight when the day starts. So you have to make changes in your sql query as follows:
    SELECT * FROM atable WHERE cast(Date as date) BETWEEN
    @Date1 AND @Date2
    And use Dim a = DateTimePicker1.Value.ToString("yyyy-MM-dd") in code to pass the FROM and TO dates.
    Hope it helps.
    Shashikant Gurav

  • #764888
    Hai Ronoer A Ahong,
    Fir filtering using the date-picker values, the value of the date-picker and the database values must be in the same format.
    To do so, you need to convert the values to a format when using in the query and use the same format for the database fields values.
    Like below:

    SELECT * FROM atable WHERE YourFormat(Date) >= YourFormat(@Date1) AND and YourFormat(Date) <= YourFormat(@Date2)

    Keep in mind that both side the format is same.
    Hope it will be helpful to you.

    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)

  • #764911
    Hi Shashikant Gurav,
    I already tried ur code example but i get and error on the CAST.

    Hello Pawan Awasthi(DNS MVM),
    I have try ur code and this is error when i executing it: 'dd' is not a recognized function name. dd is for my date format


  • #764938

    try this code

    SELECT * FROM atable WHERE Date1 BETWEEN '2016-03-01 02:48:13 AM' AND '2016-03-25 02:48:13 PM'

    use test
    --dROP table atable
    create table atable
    date1 datetime

    INSERT INTO atable VALUES ('AAAA','2016-03-01 02:48:13')
    INSERT INTO atable VALUES ('BBBB','2016-03-25 02:48:13')
    INSERT INTO atable VALUES ('AAAA','2016-03-28 02:48:13')

    working good try this implemented in your page and let me know.

    Name : Dotnet Developer-2015
    Email Id

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

  • #764954
    Can you please tell which error did u get on CAST? and which SQL server you are using?
    Shashikant Gurav

  • #764956
    I have already fix this issues. I change my datetimepicker format property into custom then give this format 'yyyy-MM-dd hh:mm:ss tt' instead of in the code. finally I set default value for the hour into start date 'yyyy-MM-dd 00:00:00 tt' and the end date '11:59:59 PM'(this is for 12 hours system). After I done it I just call the date timepicker like this
    Dim a = DateTimePicker1.value()
    Dim b = DateTimePicker2.value()
    Thank You all.

Sign In to post your comments