topic Re: Converting SQL Date Format in QlikView

Converting SQL Date Format

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Converting SQL Date Format

sunny_talwar — Mon, 20 Jun 2016 21:54:23 GMT

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

Re: Converting SQL Date Format

Mon, 20 Jun 2016 22:14:02 GMT

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.

Re: Converting SQL Date Format

Tue, 21 Jun 2016 04:29:00 GMT

Try like this

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

Re: Converting SQL Date Format

Digvijay_Singh — Tue, 21 Jun 2016 05:28:39 GMT

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.

Re: Converting SQL Date Format

varshavig12 — Tue, 21 Jun 2016 05:42:56 GMT

=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

Re: Converting SQL Date Format

varshavig12 — Tue, 21 Jun 2016 05:48:07 GMT

Can you give us one example of your data ?

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

Re: Converting SQL Date Format

Anonymous — Tue, 21 Jun 2016 06:34:05 GMT

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!

Re: Converting SQL Date Format

Tue, 07 May 2019 19:32:42 GMT

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,

Re: Converting SQL Date Format

Anonymous — Wed, 22 Jun 2016 06:16:03 GMT

Hi Julian,

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

Thanks!

Regards!

Re: Converting SQL Date Format

varshavig12 — Wed, 22 Jun 2016 07:05:54 GMT

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

The desired format is MM/DD/YY

Output:

try this:

=Date(Timestamp(Timestamp#('05 JUN 14 14:49:47','DD MMM YY hh:mm:ss'),'DD/MMM/YY hh:mm:ss tt'),'MM/DD/YY')

Re: Converting SQL Date Format

Thu, 23 Jun 2016 17:49:30 GMT

Varsha-- this actually worked to convert the format, but appears to be only hiding the timestamp. I can tell because when I create a list box I have a ton of duplicate dates..

Thanks

Re: Converting SQL Date Format

Thu, 23 Jun 2016 17:51:41 GMT

Input from SQL:

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

Desired Output:

6/5/14 OR MM/DD/YY

Re: Converting SQL Date Format

Thu, 23 Jun 2016 19:40:41 GMT

this one worked.. thanks!