How to use SUB() String Function to Extract Date Month

1

How to use substring function for extracting value of a month from a formatted date dd/mm/yyyy

2

Better to use MONTH() function and not worry over the format used for displaying the date.

2 Likes
3

I agree with Lee that if you have the value as a DATE type using MONTH(MyDate) is best, read the Help.

To answer your question about SUB() … The Help on SUB() should be your first stop, and then try some code using SUB() to see what happens.

The syntax is SUB(String, Start Position, Length) so:

DateString = FORMAT(MyDate,@d06)  !formatted dd/mm/yyyy
MonthPart  = SUB(DateString,4,2)  !position: 1234567890  Sub( Pos 4, length 2 )
MonthNumber = MONTH( MyDate )     !Better to use Month() function

You could also use a String Slice [ start : end ]. That is less safe compared to SUB() i.e. bad numbers will cause an error or possible crash. So best to avoid. Read the Help on Slice for a complete understanding.

DateString = FORMAT(MyDate,@d06)  !formatted dd/mm/yyyy
MonthPart  = DateString[4:6]      !position: 1234567890  Slice [ start 4 : end 6 ]
2 Likes
4

If the date is in a formatted string, then you can’t use the MONTH function directly. If you know the format is actually dd/mm/yyyy, then you could use DEFORMAT to help:

  M = MONTH(DEFORMAT(DateString, @D6))

Or you can just SUB:

  M = SUB(DateString, 4, 2)
5

If a date is defined as for example ‘26.15.2025’ (valid date), then SUB(date, 4, 2) returns 15, but MONTH(DEFORMAT(date, @d6.)) returns 3 (3rd month of a next year).

6

Perhaps DEFORMAT will accept 26.15.2025, but I wouldn’t call it a valid date. :thinking:

7

“Valid” in Clarion terms I mean, just another date form, see “DATE (return standard date)” help topic.

8

Yes, I understand it’s valid, at least for as a temporary state, until Clarion shifts those extra 12 months into the year portion, but I highly doubt it’s being stored to the O.P. in that format. :man_shrugging:

9

DATE( M,D,Y ) will fix a Month greater than 12. It will not work with a Month <= Zero, it returns -1.

DATE() can be passed Day <= Zero and it will work right. Of course you could simply subtract, or add, days from the Date serial number.

You can try various date values in Clarion functions without writing code using my tool:

10

Many many thanks to all the respondents. The issue is resolved very satisfactoriIy. I have been using Clarion since DOS Version. I have used this function many times in the past too. However, past age 80, and as I have not it used for last 20 years and kept committing some silly errors. Thanks once again.

3 Likes