Convert a transaction date to the first day of that month

[#1]

Hi everyone,

Can anyone tell me how I can convert a transactional date to always the first day of that particular month?

Some examples:

• 25/05/2015 should now become 01/05/2015
• 12/02/2014 should now become 01/12/2014

The real date formation that I'm currently loading in QV is YYYYMMDD (e.g. 20150528)

This is in the load script converted to DDMMYY (28052015) by means of the below but now I want the system to make a new date so that it will read 01052015

(makedate(left(Trim([Invoice Date]),4),mid(trim([Invoice Date]),5,2), right(Trim([Invoice Date]),2))) as Date

Any suggestions are welcome.

Thanks,

[petter]

Have a look at the MonthStart() function

[aveeeeeee7en]

Hi

Use this:

MonthStart(Date(YourDateField,'DD/MM/YYYY')) AS New_Date

This will Resolve your issue.

Regards

Av7eN

[#4]

Hi,

Try using MonthStart function.

MonthStart([Invoice Date]).

Regards,

Jemimah

[aveeeeeee7en]

Here is the Example:

LOAD *, MonthStart(Date(Date,'DD/MM/YYYY')) AS New_Date Inline [

Date

41975

42149

42329

];

Result:

Hope that helps.

Regards
Av7eN

[robert_mika · Accepted solution]

Another way

=date(date#('01'&MID('20150528',5,2)&LEFT('20150528',4),'DDMMYYYY'),'DD/MM/YYYY')

[ankitaag]

Hi,

Try

LOAD MonthStart(Date#(Dates,'DDMMYYY')) as Date

FROM Table

This will work..

Thanks and Regards,

Ankita

[#8]

Hi everyone,

Thanks for all your input. I tried the solution as suggested by Robert Mika which worked great.

Regards,

René

[MarcoWedel]

Hi,

another solution could be:

LOAD *,
    Date(MonthStart(Date),'DDMMYY') as MonthStart;
LOAD Date(Date#([Invoice Date], 'YYYYMMDD'),'DDMMYY') as Date
INLINE [
    Invoice Date
    20140109
    20140212
    20150525
    20150528
    20151231
];

hope this helps

regards

Marco