NetTalk Central

NetTalk Web Server => Web Server - Ask For Help => Topic started by: Richard I on March 18, 2025, 08:49:54 PM

Title: Coding a date field to be a particular day date
Post by: Richard I on March 18, 2025, 08:49:54 PM

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

Title: Re: Coding a date field to be a particular day date
Post by: Niels Larsen on March 18, 2025, 10:27:52 PM

Maybe something like this

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

Title: Re: Coding a date field to be a particular day date
Post by: Richard I on March 18, 2025, 11:05:48 PM

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

Title: Re: Coding a date field to be a particular day date
Post by: Niels Larsen on March 18, 2025, 11:19:20 PM

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

Title: Re: Coding a date field to be a particular day date
Post by: Richard I on March 19, 2025, 12:40:34 AM

Thanks but no not yet!

Title: Re: Coding a date field to be a particular day date
Post by: Niels Larsen on March 19, 2025, 01:27:18 AM

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)

Title: Re: Coding a date field to be a particular day date
Post by: Richard I on March 23, 2025, 08:43:38 PM

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