Date format

[SatyaPaleti]

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

[sunny_talwar]

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

[SatyaPaleti]

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

[MK_QSL]

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

[sunny_talwar]

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