2

I am writing SQL code to extract data for effective start date greater than or equal to 24/09/2018 00:00:00. My effective_start_date data type is in varchar2. Below is the code I am using:

TO_DATE(TRUNC(effective_start_date),'DD/MM/YY') >='24/09/2018 00:00:00')
Raj
  • 23
  • 5
  • Hi Lurker, thanks for your response. – Raj Jan 08 '19 at 11:53
  • My data contains effective start date column with date format dd/mm/yyyy 00:00:00 and data type is varchr2. So when i trying to give condition >=24/09/2018 00:00:00, the result out put bringing all entries. – Raj Jan 08 '19 at 11:55
  • Fix the table. This design is broken as is. It's **never** a good idea to store date values as string (varchar) types. – Joel Coehoorn Jan 29 '19 at 03:51
  • can you indicate your database- sql server, mysql etc – Gauravsa Jan 29 '19 at 04:35
  • **Never** store date or timestamp values in a `varchar` column. As Joel has mentioned you should rather fix your broken database design. –  Jan 29 '19 at 07:39

2 Answers2

0

try this:

select case
 when  Cast(effective_start_date as dateTime) >= cast('2018/09/24 00:00:00' as dateTime) 
 then 1 --your business
 else 
 0  --your business
 end
mhd.cs
  • 711
  • 2
  • 10
  • 28
0

You can try this: 

trunc(to_date( effective_start_date, 'DD/MM/YYYY HH24:MI:SS' )) >= to_date('24/09/2018 00:00:00', 'DD/MM/YYYY HH24:MI:SS')

Storing datetime as varchar is very wrong.

Gauravsa
  • 6,330
  • 2
  • 21
  • 30