Nvarchar to Date

[#1]

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

[jagan]

Hi,

Try like this

Data:

LOAD

*,

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

SELECT

*

FROM DataSource;

Regards,

Jagan

[Chanty4u]

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.

[#4]

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

[jagan · Accepted solution]

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);

[Chanty4u]

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",

[Kushal_Chawda]

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);