0

how I'm going to convert the date into string ? my database is plsql and the date was insert from basic date picker which i assign as bdp1.

select * from smsdw.lot_act where (tran_dttm <= to_date('4/20/2012', 'MM/DD/YYYY') and tran_dttm > to_date('4/19/2012' ,'MM/DD/YYYY'))
Husna5207
  • 449
  • 3
  • 10
  • 28
  • my input that i key-in from basic date picker is read as date not a string, but in my db is in string, so i need to convert my input as string. – Husna5207 Jun 28 '13 at 04:29
  • Your query is correct. What's the problem? – Ben Jun 28 '13 at 08:09
  • if i want to search the date '4/19/2012' yes, i can get the result. but if i want to rephrase it as parameter i can't. this is query from oracle. – Husna5207 Jun 28 '13 at 08:41
  • Why is your date stored as a string in your database? If it were stored as a date, you wouldn't have this problem. – Chris Dunaway Jun 28 '13 at 15:09

1 Answers1

1

Your format and your string don't agree.

You need a 0 in front of the 4, like this: 04/20/2012.

Or you need to adjust your format, like this: M/D/YYYY
On second look, the to_date() documentation doesn't show support for the single M. You need to include the leading 0 with your month, and a single D for the day would be extremely foolish.

An update based on your comment. Let me lead, though, by saying that using string concatenation to substitute data into queries like this is evil and likely to lead your app being hacked.

Dim queryString As String = "select * from smsdw.lot_act where (tran_dttm <= to_date( '" & bdp1.ToString("MM/dd/yyyy") & "','MM/DD/YYYY') and tran_dttm > to_date( '" & bdp1.ToString("MM/dd/yyyy") & "' ,'MM/DD/YYYY')"

As a bonus, doing this the right away avoids the need to worry about date formats at all:

Dim queryString As String = "select * from smsdw.lot_act where tran_dttm <= :bdp1 and tran_dttm > :bdp1 "

command.Parameters.Add(":bdp1", SqlDbType.DateTime).Value = bdp1

But again: different libraries may handle this slightly different. Some require the : character, some don't allow it, and some work on the order, rather than the name (meaning you would need to add the parameter twice).

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
  • ok i'm try to convert like this but its doesn't work >> Dim queryString As String = "select * from smsdw.lot_act where (tran_dttm <= to_date( '" & bdp1.Month & "/" & bdp1.Date & "/" & bdp1.Year & "/,'DD/MM/YYYY') and tran_dttm > to_date( '" & bdp1 & "' ,'MM/DD/YYYY') – Husna5207 Jun 28 '13 at 04:34
  • @Husna5207 that code is vulnerable to sql injection. It's practically begging to get hacked. Time to read up on query parameters/prepared statements. – Joel Coehoorn Jun 28 '13 at 04:36
  • Also: you just swapped month and day, which wasn't what I suggested at all. This does nothing to add a leading 0. – Joel Coehoorn Jun 28 '13 at 04:39
  • The correct way to handle this depends on the context, and how you submit that the string to the db server... but google for parameterized queries or prepared statements. – Joel Coehoorn Jun 28 '13 at 04:42
  • +1 - Parameterised query is the way to go here http://stackoverflow.com/questions/12930170/easier-way-to-send-parameterised-query-to-database – Matt Wilko Jun 28 '13 at 08:43