Date Format in SQL Query

Question

I need to use "Mitsubishi Electric Trane HVAC US LLC"."Mitsubishi Electric Trane HVAC US LLC".Contact.CreatedDate = CurDate()

Created Date is formatted yyyy-mm-dd xx:xx:xx. I need to convert it to yyy-mm-dd so I can compare to CurDate () function.

I have tried Date, DateTimeParse and DateTimeFormat.

None of them worked. TIA!

latimo1 · Answer

I got this figured out today:

Date = 10-2-2023

Cast(Curdate() - 1 as Date),

Cast(Curdate() - 2 as Date),

Cast(Curdate() - 3 as Date),

Cast(Curdate() - 4 as Date),

Cast(Curdate() - 5 as Date)

Yields:

EXPR_1          EXPR_2          EXPR_3          EXPR_4            EXPR_5

2023-10-01  2023-09-30  2023-09-29  2023-09-28            2023-09-27

latimo1 · Answer

Kamen:

It is DateTime yyyy-mm-dd xx:xx:xx.  Please note this is not a tool issue, it is a SQL issue.

select "Mitsubishi Electric Trane HVAC US LLC"."Mitsubishi Electric Trane HVAC US LLC".Contact.Top_Level_Account__c,
"Mitsubishi Electric Trane HVAC US LLC"."Mitsubishi Electric Trane HVAC US LLC".Account.Name,
"Mitsubishi Electric Trane HVAC US LLC"."Mitsubishi Electric Trane HVAC US LLC".Account.ParentId,
"Mitsubishi Electric Trane HVAC US LLC"."Mitsubishi Electric Trane HVAC US LLC".Contact.Id,
"Mitsubishi Electric Trane HVAC US LLC"."Mitsubishi Electric Trane HVAC US LLC".Contact.CreatedDate
from "Mitsubishi Electric Trane HVAC US LLC"."Mitsubishi Electric Trane HVAC US LLC".Contact, "Mitsubishi Electric Trane HVAC US LLC"."Mitsubishi Electric Trane HVAC US LLC".Account
where "Mitsubishi Electric Trane HVAC US LLC"."Mitsubishi Electric Trane HVAC US LLC".Contact.Top_Level_Account__c <> '0010Z00001yRFH5QAO'and
"Mitsubishi Electric Trane HVAC US LLC"."Mitsubishi Electric Trane HVAC US LLC".Contact.CreatedDate = CurDate()

KamenRider · Answer

Hi @latimo1

What is the data type of your date, is it string or date? Please see below my solution.

Hope this helps

Kamen