Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

Nvarchar to Date

Hi All,

I have a datefield in the below format.

Dec  1 2015  2:10PM

I need to filter data while loading. How do i convert it into date field.

In the where condition its not filtering bcoz its not in date format.

Please help me with this.

Regards,

Keerthi KS

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Nvarchar to Date

HI,

Try like this

Table1:

Load *

Where LastModifiedOn >=TimeStamp#('01/02/2016 01:00AM', 'DD/MM/YYYY hh:mmTT');

LOAD

    Date(Date#(LastModifiedOn, 'MMM D YYYY hh:mmTT'),'DD/MM/YYYY hh:mmTT') as LastModifiedOn

FROM

[..\10_data_sources\TEPurchaseHeaderStructure.qvd]

(qvd);

View solution in original post

6 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: Nvarchar to Date

Hi,

Try like this

Data:

LOAD

*,

Date(TimeStamp#(DateFieldName, 'MMM D YYYY hh:mmTT')) AS Date_New;

SELECT

*

FROM DataSource;

Regards,

Jagan

Highlighted
Chanty4u
Esteemed Contributor III

Re: Nvarchar to Date

Date(Floor (Date#( Field'MMM D YYYY hh:mmTT')) , 'DD/MM/YYYY')  as new date

or

SET DateFormat='DD/MM/YYYY'; // You can set whatever format you want-date or timestamp

  • Date#() is used for reading a date format
  • Date() is used for defining the internal date format for display.
Highlighted
Not applicable

Re: Nvarchar to Date

Hi All,

I tried something like below but date is not filtering

Table1:

Load *

Where LastModifiedOn >='01/02/2016 01:00AM';

LOAD

    Date(Date#(LastModifiedOn, 'MMM D YYYY hh:mmTT'),'DD/MM/YYYY hh:mmTT') as LastModifiedOn

FROM

[..\10_data_sources\TEPurchaseHeaderStructure.qvd]

(qvd);



But still LastModifiedOn is not filtering .


Regards,

Keerthi KS

MVP & Luminary
MVP & Luminary

Re: Nvarchar to Date

HI,

Try like this

Table1:

Load *

Where LastModifiedOn >=TimeStamp#('01/02/2016 01:00AM', 'DD/MM/YYYY hh:mmTT');

LOAD

    Date(Date#(LastModifiedOn, 'MMM D YYYY hh:mmTT'),'DD/MM/YYYY hh:mmTT') as LastModifiedOn

FROM

[..\10_data_sources\TEPurchaseHeaderStructure.qvd]

(qvd);

View solution in original post

Highlighted
Chanty4u
Esteemed Contributor III

Re: Nvarchar to Date

Alt(

    Date(Floor(Date#("LastModifiedOn",'MMM D YYYY hh:mmTT')),'DD/MM/YYYY hh:mmTT'),

    Date(Floor(Date("LastModifiedOn",''MMM D YYYY hh:mmTT')),'DD/MM/YYYY hh:mmTT')

    ) as "LastModifiedOn",

Highlighted

Re: Nvarchar to Date

You are passing the string in where clause which you are trying to compare with time format, hence the condition is not working. Try to convert the string to the time format using Timestamp# fucntion so that It will be compared with your Date field


Table1:

Load *

Where LastModifiedOn >=timestamp#('01/02/2016 01:00AM','DD/MM/YYYY hh:mmTT');

LOAD

    timestamp(timestamp#(LastModifiedOn, 'MMM D YYYY hh:mmTT')),'DD/MM/YYYY hh:mmTT') as LastModifiedOn

FROM

[..\10_data_sources\TEPurchaseHeaderStructure.qvd]

(qvd);