Coding a date field to be a particular day date

[Richard I]

I want to change a date in a month by code  say 23/07/2024 to 01/07/2024

In the table there could be a range of dates and I want them all to become the first of the respective month and year
Thanks

Cheers
Richard

[Niels Larsen]

Maybe something like this

NewDate = DATE(MONTH(OriginalDate),1,YEAR(OriginalDate))

[Richard I]

Thanks for that Niels, I need it in the format dd/mm/yyyy
I think your expression will return mm/dd/yyyy ?
Could then  format newdate as  @0D6 ?
Would that work?
Cheers,
Richard

[Niels Larsen]

format(DATE(MONTH(OriginalDate),1,YEAR(OriginalDate)),@D06)

[Richard I]

Thanks but no not yet!

[Niels Larsen]

You should be aware that FORMAT returns a string and not a date.
A date in Clarion is represented as number of days elapsed since December 28, 1800.
What you are trying will not work.
You should remove FORMAT from your import and only use it when data is to be displayed on the screen or in a report (or exported in a csv file)

[Richard I]

Thanks Niels for the heads up  re "DAY"
FYI
The Code is now sorted , provided the date column in the CSV file is changed to dd/mm/yyyy

herewith for your interest

        SET(Lineitems)
        Access:Lineitems.usefile
        OPEN (Lineitems)
        Relate:ImportCSV.OPEN
        SET(ImportCSV)
        LOOP until Access:ImportCSV.Next()
        Lin:UserName  = p_web.GSV('Username')
        Lin:Quantity      = 1
        Lin:Product        =  ImpCSV:Label3                                             
        Lin:Price             =  ImpCSV:Label6
        Lin:IncomeItem  = 0
        IF Lin:Price > 1 THEN  Lin:IncomeItem  = 1 END
        IF LIN:Price < 1 THEN LIN:Price = (LIN:Price * -1 ) END
        Lin:Total              = Lin:Price
        Lin:date                = deformat( ImpCSV:Label7),@D6)
        locNewDate        = LIN:Date 
        locdaydate          =   DAY(Lin:Date)   
        LIN:Date              =   (locNewDate - locdaydate) +1   
        INV:Date =    Lin:date   
        IF access:Invoice.Fetch(INV:byDateDescending) = LEVEL:Benign
            Lin:InvoiceNumber = INV:ID
        END
        Access:LineItems.Insert()     
        END
  loc:Alert = 'Your Bank Statement has been Imported.'

Regards,
Richard