locked
Date comparision RRS feed

  • Question

  • User-1516926156 posted

    Hi all

    In my program am trying to select all the records that are entered just Today.Since when I write the following code it does not select the selected rows entred Today.Here is my code:

     Dim sqlQ As String = "SELECT [user_name], [leave_name], [start_date], [end_date] FROM [users_holidays_view] WHERE leave_holiday_flag <>1 AND (start_date= ('" & Date.Today & "') )  ORDER BY [start_date]"
    

    start_date is the field of start date vacation.

    so any help?

    Thanks

    Regards

    Monday, January 6, 2014 5:13 AM

Answers

  • User1401801381 posted

    Hi

    I assume start_date is a datetime, so it has the time with

    in your query you try to compare a datetime that could be "06/01/2014 09:20:11" with Date.Today which will be "06/01/2014 00:00:00"

    you can replace your query by this one which compare the date part only 

    SELECT [user_name], [leave_name], [start_date], [end_date]
     FROM [users_holidays_view]
     WHERE leave_holiday_flag <>1 AND CONVERT(varchar(8),start_date,112) = CONVERT(varchar(8), GETDATE(), 112) ORDER BY [start_date]

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 6, 2014 5:22 AM
  • User-1516926156 posted

    Hi all 

    Thanks all for your replies

    I have tried the following and its worked perfect

    Dim sqlQ As String = "SELECT [user_name], [leave_name], [start_date], [end_date] FROM [users_holidays_view] WHERE leave_holiday_flag <>1 AND (convert(varchar(10), start_date, 102 ) = convert(varchar(10), getdate(), 102) )  ORDER BY [start_date]"
    

    Regards

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 7, 2014 3:21 AM

All replies

  • User-760709272 posted
     Dim sqlQ As String = "SELECT [user_name], [leave_name], [start_date], [end_date] FROM [users_holidays_view] WHERE leave_holiday_flag <>1 AND (day(start_date)=date(GetDate()) and month(start_date)=month(GetDate()) and year(start_date) = year(GetDate())  ORDER BY [start_date]"

    Monday, January 6, 2014 5:16 AM
  • User1401801381 posted

    Hi

    I assume start_date is a datetime, so it has the time with

    in your query you try to compare a datetime that could be "06/01/2014 09:20:11" with Date.Today which will be "06/01/2014 00:00:00"

    you can replace your query by this one which compare the date part only 

    SELECT [user_name], [leave_name], [start_date], [end_date]
     FROM [users_holidays_view]
     WHERE leave_holiday_flag <>1 AND CONVERT(varchar(8),start_date,112) = CONVERT(varchar(8), GETDATE(), 112) ORDER BY [start_date]

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 6, 2014 5:22 AM
  • User-1509636757 posted

    Your query is correct but sinnce I believe there can be date format related issue; I suggest you check day, month and year separately in your code as:

    Dim sqlQ As String = "SELECT [user_name], [leave_name], [start_date], [end_date] FROM [users_holidays_view] WHERE leave_holiday_flag <>1 AND DATEPART(DD,start_date)= '" & Date.Today.Day & "' AND DATEPART(MM,start_date)= '" & Date.Today.Month & "'AND DATEPART(YYYY,start_date)= '" & Date.Today.Year & "' ORDER BY [start_date]"

    hope it helps./.

    Monday, January 6, 2014 5:29 AM
  • User-1516926156 posted

    Hi all 

    Thanks all for your replies

    I have tried the following and its worked perfect

    Dim sqlQ As String = "SELECT [user_name], [leave_name], [start_date], [end_date] FROM [users_holidays_view] WHERE leave_holiday_flag <>1 AND (convert(varchar(10), start_date, 102 ) = convert(varchar(10), getdate(), 102) )  ORDER BY [start_date]"
    

    Regards

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 7, 2014 3:21 AM
  • User-1516926156 posted

    Hi sam_xiii

    It's worked

    thanks.

    Tuesday, January 7, 2014 3:48 AM