Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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);
Hi,
Try like this
Data:
LOAD
*,
Date(TimeStamp#(DateFieldName, 'MMM D YYYY hh:mmTT')) AS Date_New;
SELECT
*
FROM DataSource;
Regards,
Jagan
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
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
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);
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",
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);