Issue with Makedate function

[Anonymous]

Hello

I have a test data as follows:

DateTest

1/05/2018 03:05:45 a.m

2/05/2018 03:07:55 a.m

3/05/2018 03:30:25 a.m

4/05/2018 03:30:25 a.m

5/05/2018 03:30:25 a.m

6/05/2018 03:30:25 a.m

7/05/2018 03:30:25 a.m

8/05/2018 03:30:25 a.m

9/05/2018 03:30:25 a.m

10/05/2018 03:30:25 a.m

11/05/2018 03:30:25 a.m

12/05/2018 03:30:25 a.m

13/05/2018 03:30:25 a.m

14/05/2018 03:30:25 a.m

15/05/2018 03:30:25 a.m

16/05/2018 03:30:25 a.m

17/05/2018 03:30:25 a.m

18/05/2018 03:30:25 a.m

I am using a code:

Test:

LOAD DateTest,

makedate(Date#(year(DateTest),month(DateTest)),'MM-YYYY') as RevisedDate

FROM

(ooxml, embedded labels, table is Sheet1);

It does not return any output. I also tried running the function without Date#. The same thing happened.

Got a blank listbox in both cases.

Please help.

Regards

Mahendra

[duncanblaine]

Hi Mahendra
The Date# function won't automatically know what format your date is in
In this example, I would keep it simple and use SubField to extract the first part of the date, ignoring the time part.
I've used a preceding load to create the RevisedDate just to reduce cluttered code.

This script should work for you if you copy and paste:

Test:

LOAD DateTest, Date(makeDate(Year(DateTestAsDate),Month(DateTestAsDate)),'MM-YYYY') as RevisedDate;

LOAD

DateTest,

Date(Date#(SubField(DateTest,' '),'D/MM/YYYY')) as DateTestAsDate

FROM

(ooxml, embedded labels, table is Sheet1);

[duncanblaine]

An alternative to my previous reply, that might work better with your script and retain the time element in your date..

The date format is unusual in that it has 'a.m' not 'am'. However as you only really need to 'a' or 'p', you can remove the '.m' using Replace().
Then your date will be a recognised format 'D/MM/YYYY hh:mm:ss t'.
You still need to pass that to Date# as follows:

Date#(Replace(DateTest,'.m',''),'D/MM/YYYY hh:mm:ss t')

[Anonymous]

Hi Mahendra

Try this

Date(TimeStamp#(Replace(DateTest,'.m','m'),'D/MM/YYYY hh:mm:ss tt'),'MM-YYYY') as RevisedDate

[Anil_Babu_Samineni]

PFA

Load *, Num(Month(Left(DateTest,10))) & '-' & Year(Left(DateTest,10)) as Output From Source ...

[Anonymous]

Hi

Thanks to all for such prompt response.

However I apologize for not describing my requirement properly.

eg: 10/05/2018 03:30:25 a.m.

I need it to be represented as 5-May-2018 or 5-May-18

Regards

Mahendra

[Anil_Babu_Samineni]

Like this?

Date(Left(DateTest,10),'DD') & '-' & Month(Left(DateTest,10)) & '-' & Year(Left(DateTest,10)) as Output

OR

Date(Left(DateTest,10),'DD-MMM-YYYY') as Output

[Anonymous]

Hi

PFA

[sasiparupudi1]

Try

Date (Date#(Subfield(DateTest,' ',1),'DD/MM/YYYY'),'DD-MMM-YYYY')

[Anonymous]

I am trying to run a script:

sub TraceRowCount (SourceTable)

//Writes the number of rows in SourceTable

//to the Script Execution Progress window

if '$(SourceTable)' <> ''

let vNoOfRows = NoOfRows('$(SourceTable)');

trace >>> Number of rows in $(SourceTable):$(vNoOfRows);

let vNoOfRows=Null();

Else

trace >>> No table name specified;

End if

End sub

It is throwing an error as follows: