Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Converting SQL Date Format

I have a date field which I am pulling in via SQL script as follows:

   TO_CHAR(A.ACTUAL_OPEN_EST_TIME, 'DD MON YY HH24:MI:SS')  As "Change Actual Open Time",

Above my SQL code, I'm attempting to convert this to a useable date format using the following code:

Date(Date#(Left([Change Actual Open Time], 10), 'MM/DD/YYYY'), 'MM/DD/YY') as ChangeOpenDate

I'm getting all blank values.. not sure what I'm doing wrong here.

1 Solution

Accepted Solutions

Without any manipulations what do you get? A date like this ->?

If it does, then try like this:

Date(Floor(Date#([Change Actual Open Time], 'DD MMM YY hh:mm:ss')), 'MM/DD/YY') as ChangeOpenDate

For time may be this:

Time(Frac(Date#([Change Actual Open Time], 'DD MMM YY hh:mm:ss'))) as ChangeOpenTime

View solution in original post

13 Replies

Without any manipulations what do you get? A date like this ->?

If it does, then try like this:

Date(Floor(Date#([Change Actual Open Time], 'DD MMM YY hh:mm:ss')), 'MM/DD/YY') as ChangeOpenDate

For time may be this:

Time(Frac(Date#([Change Actual Open Time], 'DD MMM YY hh:mm:ss'))) as ChangeOpenTime

View solution in original post

Not applicable

Not sure I understand your question..  The original field from the SQL script is formatted like this:

I tried the first line of code you sent over but I'm still getting blanks.

Not applicable

Try like this

Date(Date#(Left([Change Actual Open Time], 9), 'DD MMM YY'), 'MM/DD/YY') as ChangeOpenDate

Master III
Master III

The function Date#(Date,'Format') is used to interpret the source date, so the second parameter 'format' here must be exactly same as it comes from the source(as correctly suggested by others). Date# will change the source field to date type only if Qlikview is able to interpret the source format properly. The second date helps to convert the date field in desired format.

Specialist
Specialist

=date(date#([Change Actual Open Time],'DD MMM YY hh:mm:ss'),'DD/MM/YY')

Example:

if [Change Actual Open Time] = '30 Apr 16 11:45:50'

Then:

=date(date#('31 Apr 16 11:45:50','DD MMM YY hh:mm:ss'),'DD/MM/YY')

Output:

30/04/16

Specialist
Specialist

Can you give us one example of your data ?

So that we can help you in getting the desired output.

Specialist III
Specialist III

Hi Julian,

Try this:

Date(Date#([Change Actual Open Time],'DD MMM YY hh:mm:ss'),'DD/MM/YYYY') AS YourDateField

Time(Date#([Change Actual Open Time],'DD MMM YY hh:mm:ss'),'hh:mm:ss') AS YourTimeField

If you have a line break between year and hour, try this:

Date(Date#([Change Actual Open Time],'DD MMM YY'&CHR(10)&'hh:mm:ss'),'DD/MM/YYYY') AS YourDateField

Time(Date#([Change Actual Open Time],'DD MMM YY'&CHR(10)&'hh:mm:ss'),'hh:mm:ss') AS YourTimeField

Regards!

Not applicable

I tried all of the suggestions above but I'm still getting blanks.. Here is my code to provideo some context.  The original date field format is like this:

05 JUN 14 14:49:47 or DD MON YY hh24:min:ss

 

The desired format is MM/DD/YY

 

OLEDB CONNECT TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=True;User ID=D494263;Data Source="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=psin4p789.svr.us.[redacted].net)(PORT=6336))(CONNECT_DATA=(SERVICE_NAME=CPRP_RPT_SERV.GTI.whem.[redacted].net)))";Extended Properties=""] (XPassword is WXAObFFKNDTITbRMXRSA);

Changes:
NoConcatenate LOAD
[Change ID],
[Change Number],
//Date(Date#(Left([Change Actual Open Time], 10), 'MM/DD/YYYY'), 'MM/DD/YY') as ChangeOpenDate
//Date(Floor(Date#([Change Actual Open Time], 'DD MMM YY hh:mm:ss')), 'MM/DD/YY') as ChangeOpenDate
//Date(Date#(Left(Date([Change Actual Open Time], 10),'DD MON YY HH24:MI:SS'),'MM/DD/YY')) as ChangeOpenDate
Date#(Date(Left([Change Actual Open Time], 9), 'DD MON YY HH24:MI:SS'), 'MM/DD/YY') as ChangeOpenDate1,

;
SQL

SELECT DISTINCT

A.CHANGE_ID As "Change ID",
A.CHANGE_NUMBER As "Change Number",
A.TITLE As "Change Title",
A.DESCRIPTION As "Change Description",
A.OWNING_LOB_NAME As "Change Owner Full LOB",
B.CATEGORY_NAME As "Change Urgency",
B.IMPACT As "Change Risk Impact",
B.STATUS_NAME As "Change Status",
TO_CHAR(A.ACTUAL_OPEN_EST_TIME, 'DD MON YY HH24:MI:SS')  As "Change Actual Open Time",
C.ENVIRONMENT_ID As "Change Environment ID",
A.STATUS_ID As "Change Status ID",
EXTRACT(DAY FROM A.ACTUAL_OPEN_EST_TIME) As "Change Day",
EXTRACT(MONTH FROM A.ACTUAL_OPEN_EST_TIME) As "Change Months",
EXTRACT(YEAR FROM A.ACTUAL_OPEN_EST_TIME) As "Change Year",
OWNING_LOB_NAME,

Specialist III
Specialist III

Hi Julian,

Could you share a file with an example of your dates please?

Thanks!

Regards!