topic Re: Date format in QlikView

Date format

SatyaPaleti — Sun, 08 Nov 2015 05:26:39 GMT

Hi Friends,

I am having a field called Date it is in

10/29/2015 12:45:22 AM format

I Want to show in 29-Oct-2015 format.

What is the exact function I need to use here

Thank you,

Satya Paleti

Re: Date format

handry_orozco — Sun, 08 Nov 2015 05:51:16 GMT

Hi Friend

Try,

Table_tmp:

LOAD

Day(date) AS Day,

Month(MonthName(date)) AS Month,

Year(date) AS Year

FROM Table1.Qvd;

NoConcatenate

Table:

LOAD Day&'-'&Month&'-'&Year AS Date

Resident Table_tmp;
Drop Table Table_tmp;

bye

Re: Date format

Sun, 08 Nov 2015 06:04:07 GMT

use

=Date(DateFiled,'DD_MMM-YYYY')

Re: Date format

settu_periasamy — Sun, 08 Nov 2015 06:10:53 GMT

Try like

Date (Date#(YourDateField,'MM/DD/YYYY HH:MM:SS TT'),'DD-MMM-YYYY') as Date

Re: Date format

SatyaPaleti — Sun, 08 Nov 2015 07:04:28 GMT

Hi Settu,

Thank you for your reply but it's not working here

Thank you,]

Satya Paleti

Re: Date format

HirisH_V7 — Sun, 08 Nov 2015 07:13:08 GMT

Hi,

Date:

Load*,

TimeStamp(

Alt(

TimeStamp#(Date,'MM/DD/YYYY hh:mm:ss')

),'MM/DD/YYYY'

) as New_Date_Value

;

LOAD * INLINE [

Date

10/29/2015 12:45:22

10/30/2015 12:45:22

];

Please check this .May be helpful

Thanks,

hirish

Re: Date format

Chanty4u — Sun, 08 Nov 2015 07:15:33 GMT

hi satya,

Please chk below links

https://community.qlik.com/blogs/qlikviewdesignblog/2013/02/19/why-don-t-my-dates-work

https://community.qlik.com/message/847632#847632

hope this helps u

Regards,

Suresh

Re: Date format

Chanty4u — Sun, 08 Nov 2015 07:17:19 GMT

hi,

=Timestamp(Timestamp#('9/13/2015 2:40:49 AM', 'MM/DD/YYYY hh:mm:ss TT'), 'DD-MMM-YYYY') as date

hope this helps u

Thanks

Suresh

Re: Date format

ElizaF — Sun, 08 Nov 2015 07:28:04 GMT

Hi,

Possibly because you did not set the data format at the beginning of the script.

Please, set variable:

SET DateFormat='MM/DD/YYYY hh:mm:ss[.fff] TT';

and after try the function provided by settu_periasamy.

Re: Date format

MK_QSL — Sun, 08 Nov 2015 08:25:06 GMT

At Front End

=Date(Floor(TimeStamp#('10/29/2015 12:45:22 AM','MM/DD/YYYY hh:mm:ss TT')),'DD-MMM-YYYY')

=Date(Floor(TimeStamp#(YourTimeStampField,'MM/DD/YYYY hh:mm:ss TT')),'DD-MMM-YYYY')

At Back End (in script)

Date(Floor(TimeStamp#(YourTimeStampField,'MM/DD/YYYY hh:mm:ss TT')),'DD-MMM-YYYY') as DateField

Re: Date format

sunny_talwar — Sun, 08 Nov 2015 08:43:29 GMT

I don't see why what Settu provided should not work if the your current TimeStamp is 10/29/2015 12:45:22 AM. Is there a sample which can share where it seems to be not working for you? Cause the syntax seems to be right. The only addition which can you make is to add Floor function to remove the time part of the date (as Manish mentioned below)

Date (Floor(Date#(YourDateField,'MM/DD/YYYY HH:MM:SS TT')),'DD-MMM-YYYY') as Date

Re: Date format

SatyaPaleti — Sun, 08 Nov 2015 09:09:42 GMT

Actually I am loading data from SQL Server,

load

field 1,

field 2,

field 3,

field 4

sql select

field 1,

field 2,

field 3,

field 4

from xyz

Here field 4 is in date format i.e ., Date,'MM/DD/YYYY hh:mm:ss'

I want to show date(field 4 ) = 10/29/2015.

I am writing a code like this

load

field 1,

field 2,

field 3,

Date(field 4,'MM/DD/YYYY') as field_date

sql select

field 1,

field 2,

field 3,

field 4

from xyz

where field_date = '10/29/2015';

But I am getting error

SQL##f - SqlState: S1000, ErrorCode: 933, ErrorMsg: [Oracle][ODBC][Ora]ORA-00933: SQL command not properly ended

Plz help with this

Thank you,

Satya

Re: Date format

MK_QSL — Sun, 08 Nov 2015 09:29:39 GMT

This is complete different statement than what you have asked... Better if the question would have been asked with more accuracy and to the point to your requirements

Re: Date format

sunny_talwar — Sun, 08 Nov 2015 10:00:32 GMT

I agree with Manish‌. Your issue seems to be with SQL and you are asking to fix the date format in QlikView. Sharing the information you just shared would have saved you and other a lot of trouble.

I think you need to read some stuff related to Preceding Load. Try here: Preceding Load‌. The order of execution happens bottom up for Preceding Load. Right now in your SQL statement you are using a Where statement with a field that you will be creating in your load statement. That is not recognized since the field has not been created. So instead of using date_field, you will need to use field4. You can make the transformation from a timestamp field to a date field in SQL itself (not sure how to do it, but I am pretty sure it can be done) and equate it to your the date you want to filter out.

I hope this helps.

Best,

Sunny