-2
trunc(to_date('datefield1','mm/dd/yyyy hh24:mi:ss')) 
between
trunc(to_date(to_char('05/18/2016 08:57','mm/dd/yyyy hh24:mi:ss'),'mm/dd/yyyy,hh24:mi:ss'))
and 
trunc(to_date(to_char('05/20/2016 08:57','mm/dd/yyyy hh24:mi:ss'),'mm/dd/yyyy,hh24:mi:ss'));

I need to convert the string '05/18/2016 08:57' into date

Ankur Sinha
  • 6,473
  • 7
  • 42
  • 73
abhilash
  • 7
  • 3
  • 1
    please format your code and add which error do you get and which DBMS you are using. – Mark May 04 '18 at 08:40
  • 1
    Possible duplicate of [Convert a string date into datetime in Oracle](https://stackoverflow.com/questions/7920637/convert-a-string-date-into-datetime-in-oracle) – Ankur Sinha May 04 '18 at 08:41
  • @Mark, it looks like he is using Oracle. to_date is a function used in Oracle! – Ankur Sinha May 04 '18 at 08:42
  • Why are you storing DATE values as `varchar`? –  May 04 '18 at 09:12
  • 1
    `to_char('05/18/2016 08:57','mm/dd/yyyy hh24:mi:ss')` makes **no** sense whatsoever - you can shorten that to `''05/18/2016 08:57:00'` –  May 04 '18 at 09:12
  • sorry for the wrong requirement , I need to change "7 May, 2018 6:13:17 PM" that is the input into 'mm/dd/yyyy hh:mi' – abhilash May 07 '18 at 18:33

1 Answers1

0

Why are you converting a character string to a character string? You can do:

trunc(to_date(datefield1, 'mm/dd/yyyy hh24:mi:ss'))
    between trunc(to_date('05/18/2016 08:57', 'mm/dd/yyyy hh24:mi:ss'), 'mm/dd/yyyy,hh24:mi:ss') and 
            trunc(to_date('05/20/2016 08:57', 'mm/dd/yyyy hh24:mi:ss'), 'mm/dd/yyyy hh24:mi:ss');

It is very strange to have hh24:mi:ss when the string itself has no seconds. But Oracle allows it.

That said, I would write the logic as:

datefield1 >= date '2016-05-18' and
datefield1 < date '2016-05-21'
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • sorry for the wrong requirement , I need to change "7 May, 2018 6:13:17 PM" that is the input into 'mm/dd/yyyy hh:mi' – abhilash May 07 '18 at 18:32