topic Re: Convert DateTime format to Date only in Editor in QlikView

Convert DateTime format to Date only in Editor

DirkCtz — Thu, 26 Aug 2021 09:57:55 GMT

Hi,

I am trying to convert DateTime (2021-01-01 12:18:23) obtained from SQL connection (SSMS) to Date (2021-01-01) only in the script editor.

Tried many proposals from the Forum with no luck. Reverting to the below - but this is not a perfect solution and causes issues elsewhere in the build -

Trying to convert a.alert_date to just Date (as above)

ODBC CONNECT TO SSMS;
SELECT 'WLF Batch' as queue_summary
,Left(a.alert_date,10) as "AlertDATE"
,Day(a.alert_date) as "Alert_day"
,Month(a.alert_date) as "Alert_month"
,Year(a.alert_date) as "Alert_year"

...

Any suggestions?

Re: Convert DateTime format to Date only in Editor

Or — Thu, 26 Aug 2021 10:11:55 GMT

Load *, DayName(alert_date) as AlertDATE, Date(Floor(alert_date)) as AnotherAlertDATE;

SELECT alert_date, OtherFields

From YourTable;

This assumes your date is actually a date field, rather than a string that happens to be formatted as a date. If the latter is the case, you'll need to use date#() to convert it to a date format first, and then apply one of the other function(s).

Re: Convert DateTime format to Date only in Editor

DirkCtz — Thu, 26 Aug 2021 11:41:17 GMT

Thank you for the response. I'm not getting it to work though...

Please could you check this out for me and advise?

ODBC CONNECT TO SSMS;

Load *,
DayName(a.alert_date) as AlertDATE,
Date(Floor(a.alert_date)) as AnotherAlertDATE;

SELECT
a.alert_date
,Left(a.alert_date,10) as "AlertDATELeft"
,Day(a.alert_date) as "Alert_day"
,Month(a.alert_date) as "Alert_month"
,Year(a.alert_date) as "Alert_year"

FROM Table1 a
LEFT JOIN Table2 b ON a.ID = b.ID

Re: Convert DateTime format to Date only in Editor

Or — Thu, 26 Aug 2021 11:55:53 GMT

Sorry, but I can't check this out for you - it's a query from your database that I don't have access to. What result are you getting, and what are sample input values?

Re: Convert DateTime format to Date only in Editor

DirkCtz — Thu, 26 Aug 2021 12:21:33 GMT

Understood -
Is the construct of my query in order though?

a.alert_date is in format 2021-01-01 12:18:23

Re: Convert DateTime format to Date only in Editor

Or — Thu, 26 Aug 2021 12:24:10 GMT

Try Dayname(date#(alert_date,'YYYY-MM-DD hh:mm:ss')) - if Qlik isn't reading it as a date, this should force it to do so.