Date Conversion

[#1]

Hi

I am reading the csv file which has the date column.  Loading this data into the script.  When loading i am getting the Year, Month from this date.   But the values comes out as NULL.

The actual date fields has value. But when converting the values are not getting populated.  I did the Date# conversion also.

Can someone help on what could be the issue.

Thanks

Ramesh

[shree909]

Hi could you please post a sample of file.

or the  data column values from the csv file .

that will be easy to figure it out..

Mostly  u need to use the  Date(Date#( 'ur date filed column,/'MM/DD/YYYY'),'  to convert to the desired format') as Date

This should solve ur issue  or load a sample of data...

[tresesco]

May be your field reference is not being correct. Try 'File Wizard' to load the data and see if you can find the data at the right place.

[#4]

Hi Ana

Here is the script

ASW_MAIN:

LOAD

BillingPeriondStartDate,   This has value 9/1/2013 00:00

date#(BillingPeriodStartDate,'M/D/YYYY') as billstartdate, This is also getting the value as 9/1/2013
Year(BillingPeriodStartDate) as BillingYear,This has no value
Month(BillingPeriodStartDate) as BillingMonth, This has no value
BillingPeriodEndDate, This has value 9/30/2013 23:59

LOAD
Min(BillingPeriodStartDate) as MinDate,
Max(BillingPeriodEndDate) as MaxDate
Resident AWS_MAIN;

Both the MinDate and MaxDate are getting null values.   I checked the columns for null values and none of them have null values.

Thanks

Ramesh

[Clever_Anjos]

It hard to think a reason without knowing your data, please post a line of your csv file

[shree909]

Hi Use this

This should work

Year(Floor(date#(BillingPeriodStartDate,'M/D/YYYY')))as BillingYear,
Month(Floor(date#(BillingPeriodStartDate,'M/D/YYYY'))) as BillingMonth,

If this doesn't work load a sample file with data or csv file..

[#7]

Hi

Here is the data

InvoiceID,PayerAccountId,LinkedAccountId,RecordType,RecordID,BillingPeriodStartDate,BillingPeriodEndDate

234,22312314,1231200001,LinkedLineItem,36012341200,9/1/2013 0:00,9/30/2013 23:59

Thanks

Ramesh

[Clever_Anjos · Accepted solution]

Converting to a date

LOAD
InvoiceID,
PayerAccountId,
LinkedAccountId,
RecordType,
RecordID,
date#(BillingPeriodStartDate,'M/D/YYYY h:mm') as BillingPeriodStartDate,
date#(BillingPeriodEndDate,'M/D/YYYY h:mm') as BillingPeriodEndDate
INLINE [
    InvoiceID,PayerAccountId,LinkedAccountId,RecordType,RecordID,BillingPeriodStartDate,BillingPeriodEndDate
    234,22312314,1231200001,LinkedLineItem,36012341200,9/1/2013 0:00,9/30/2013 23:59
];