Converting SQL Date Format

[#1]

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.

[sunny_talwar · Accepted solution]

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

[#3]

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.

[#4]

Try like this

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

[Digvijay_Singh]

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.

[varshavig12]

=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

[varshavig12]

Can you give us one example of your data ?

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

[Anonymous]

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!

[#9]

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,

[Anonymous]

Hi Julian,

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

Thanks!

Regards!