Solved Better Alternative to CDate (1 Viewer)

[Pac-Man]

Hello,

Is there a better alternative to CDate function which can convert a string to a date and also asks for format of string date being input. For instance, a string "01/07/2021" can be January 7, 2021 and can also be July 1, 2021 depending upon what was the formatted when this date was saved as string. So there may be a function not only takes string date as input but also require optional info for the format of the date being input.

Best Regards,
Abdullah

 

[theDBguy]

Can't think of any. However, it might be possible to create one.

 

[Gasman]

Hello,

Is there a better alternative to CDate function which can convert a string to a date and also asks for format of string date being input. For instance, a string "01/07/2021" can be January 7, 2021 and can also be July 1, 2021 depending upon what was the formatted when this date was saved as string. So there may be a function not only takes string date as input but also require optional info for the format of the date being input.

Best Regards,
Abdullah

So supply a non ambiguous date?

MS Access: CDate Function

This MSAccess tutorial explains how to use the Access CDate function with syntax and examples. The Microsoft Access CDate function converts a value to a date.

www.techonthenet.com

 

[oleronesoftwares]

Is there a better alternative to CDate function which can convert a string to a date and also asks for format of string date being input. For instance, a string "01/07/2021" can be January 7, 2021 and can also be July 1, 2021 depending upon what was the formatted when this date was saved as string. So there may be a function not only takes string date as input but also require optional info for the format of the date being input.

The CDate function is the main way provided to convert to date, in your scenario it can be used inside an if else statement.

 

[theDBguy]

Just another thought... If you create your own CDate() function, it would probably have to be Regional Settings aware.

 

[Pac-Man]

Thanks a lot for replies.

So supply a non ambiguous date?

How can I do that? It is true I wrote ambiguous date in the post intentionally but in the database date can be any date including the dates where day is equal or less than 12.

If you create your own CDate() function, it would probably have to be Regional Settings aware.

I am not very good with programming but I wrote a simple function. Any suggestions how can I improve it?

Function myCDate(sDate as String, Optional sInputFormat as myCDateEnum = myCDateEnum.MMDDYYYY, Optional sOutputFormat as String = "MM-DD-YYYY") as Date

Dim intYear as Integer, intMonth as Integer, intYear as Integer
Select Case sInputFormat
    Case myCDateEnum.DDMMYYYY ' two public enum defined
        intYear = Right(sDate, 4)
        intMonth = Mid(sDate, 3, 2)
        intDay = Left(sDate, 2)
    Case myCDateEnum.MMDDYYYY
        intYear = Right(sDate, 4)
        intDay = Mid(sDate, 3, 2)
        intMonth = Left(sDate, 2)
End Select

myCDate = CDate(Format(DateSerial (intYear, intMonth, intDate), sOutputFormat))
End Function

 

[Gasman]

Well your dates will be set for your region surely? If you are getting dates from outside, then you would need to know their format?
Mine are in dd/mm/yyyy format, but if you set the format to "mmm dd, yyyy" then Cdate will recognise the date for what it is.?

Did you even look at that link? as that shows exactly that format, and Cdate() can work out what is a day and what is a month.

 

[Pac-Man]

Did you even look at that link? as that shows exactly that format, and Cdate() can work out what is a day and what is a month.

Yes sir I did. I might have confused the question. By text date I didn't mean those dates in which months are text like Jan, Feb etc. I means dates stored as text like CStr(Date). And yes I know the format of those dates that is why I asked for format as input of the function. Do you mean that if I have dates in DD/MM/YYYY format then I first have to change my date to this format and then CDate will take 1/7/2021 as July 1, 2021 and not January 7, 2021?

 

[Gasman]

Yes, but if you have them as dates, then they are just numbers? You can have any format you want, but they are still numbers. The format will make a string, but should not be stored as such.

The issue comes if I send you a file and the dates are actually text, you need to work out in what format. For that you would just ask me?
Or you could ask for them in that extended format if for some stupid reason I have to supply them as text. Then CDate() would work as normal.

 

[MajP]

Do you mean that if I have dates in DD/MM/YYYY format then I first have to change my date to this format and then CDate will take 1/7/2021 as July 1, 2021 and not January 7, 2021?

If in your regional settings you are set for dates in the day then month format then cdate will convert a string
"1/7/2022" to 1 July
if your regional settings are month day formats then it will convert to 7 Jan

In my case I am set in the month, day if I imported some strings that someone and it needs to be interpreted as day month then I cannot use CDate but would have to make my own function. This is why we do not use strings . If we use a date datatype. Then in my region or your region the database stores today as 44583 and it will always appear properly formatted.

 

[sonic8]

I means dates stored as text like CStr(Date).

The solutions are pretty simple:

• In the database do not store dates as text.
• If you have to write dates to text files and you can decided upon the format, use an unambiguous format like ISO (YYYY-MM-DD).
• If you must import text files from an external source, which include ambiguous dates, there must be a convention on how these date should be interpreted. Then use an Import Specification matching the intended date format.

For further reading: The Date Data Type in VBA and Access

 

[Pac-Man]

Thanks a lot everybody for your input and explanation. Stay safe and healthy.

 

[theDBguy]

Thanks a lot everybody for your input and explanation. Stay safe and healthy.

Good luck!

 

[Pac-Man]

Good luck!

So nice of you.