0

Good day! I have a problem about filtering string date in date range using variable. Here's my sample query that didn't work:

Select * from table_name where datee >= '" & result & "' and datee1 <= '" & result1 & "';

This code displays same date , 2012-12-31 and 2012-12-31.. What I want is on the greater than equal will display is 2012-12-01 and for less than or equal to is 2012-12-31.. Here's my code for date range:

Dim datee, datee1, result, result1 as string
Dim dateTime As Date
Dim dTime As Date
dateTime = Date.Parse(datee)
dTime = Date.Parse(datee1)
result = dateTime.ToString("yyyy-MM-dd")
result1 = dTime.ToString("yyyy-MM-dd")
Pauline
  • 21
  • 1
  • 2
  • 10
  • Does it work if you try with static values instead of passing the variables? In the title you mentioned your database `date` is `string`, that means you will need to convert it to a date if you want to compare that to a date.. isn't it? – bonCodigo Jan 25 '13 at 04:00
  • you can format the date like `Dim dTime1 As Date = Now Console.WriteLine(dTime1.ToString("yyyy-MM-dd"))`, and i guess you forgot to set the two `Date`s – spajce Jan 25 '13 at 06:18

1 Answers1

0

Try this please: (assuming your variables are holding proper date formats and values and database string date format is YYYY-MM-DD)

Select * from table_name where Str_to_Date(datee,'%Y-%m-%d') >= 
'" & result & "' and Str_to_Date(datee1,'%Y-%m-%d') <= '" & result1 & "'
;

Since 12-01 to 12-31 covers Month of December.. you can try:

Select * from table_name where Month(Str_to_Date(datee,'%Y-%m-%d')) = 12
;

Hence,

Select * from table_name where 
Month(Str_to_Date(datee,'%Y-%m-%d')) = " & Month(result1) & "
;

Check the demo very carefully on the query that I have used month function and the string dates format in the sample demo table:

bonCodigo
  • 14,268
  • 1
  • 48
  • 91
  • it's not working.. it still display 2012-12-31 on Str_to_Date(datee,'%Y-%m-%d') >= '" & result & "'.. what i want is 2012-12-01 – Pauline Jan 25 '13 at 05:15
  • How does your `datee` and `date1` date string look like, can you show us a sample please? – bonCodigo Jan 25 '13 at 05:16
  • I'm doing the scanning that came from excel.. sample date is Monday, December 31, 2012, it passes all date in one variable in vb.net which I converted it to date and converted again to string that will insert to db. After I inserted , '2012-12-31' string.. I will do the search query for getting the start date and the end date. – Pauline Jan 25 '13 at 05:21
  • I just want to know the format of your `datee` date string, whether it looks like `Monday, December 31, 2012` or `2012-12-31`? – bonCodigo Jan 25 '13 at 05:22
  • dateTime.ToString("yyyy-MM-dd") – Pauline Jan 25 '13 at 05:34