Convert text to date (1 Viewer)

[Leo_Polla_Psemata]

Hi,
i have a text field consisted of 6 digits as this format 210922 ,
the first two digits is the year, the next two the month and the last two the date.
I want to convert it to date value, is there any way to converted as 22/09/2021 ?
I could use string functions but, is there is any faster smarter way ??

 

[bastanu]

You should look at the DateSerial function.

 

[theDBguy]

Hi. You could also try:

CDate(Format([TextField], "00-00-00"))

Hope that helps...

 

[bastanu]

Not sure if Cdate would know which one is the year vs. the day, but something like this might work, but again might be confused with month vs. day
CDate("20" & Format([TextField], "00-00-00"))

Cheers,

 

[MajP]

Public Function CstrDate(strDate As String) As Date
  CstrDate = DateSerial(20 & Mid(strDate, 1, 2), Mid(strDate, 3, 2), Mid(strDate, 5, 2))
End Function

 

[arnelgp]

always converto ISO date or US date format:

CDate("20 & Mid$([Field], 5) & "-" & Mid$([Field], 3, 2) & "-" & Left$([Field], 2))

 

[Pat Hartman]

Arnel, I'm pretty sure the string is yymmdd. That is a typical MF format. Year is first next is month and day is last since that facilitates sorting.

 

[The_Doc_Man]

The OP specifically stated the order that he had as yymmdd (in the first post of the thread).

 

[Leo_Polla_Psemata]

Hi, just to be on the safe side, i used string functions and tailored date in the desired format. Thanks anyway for your input.

 

[Leo_Polla_Psemata]

Public Function CstrDate(strDate As String) As Date
  CstrDate = DateSerial(20 & Mid(strDate, 1, 2), Mid(strDate, 3, 2), Mid(strDate, 5, 2))
End Function

Hi, self life of this code is just 79 (or 78) more years, then disaster .

 

[The_Doc_Man]

Hi, self life of this code is just 79 (or 78) more years, then disaster .

The way I look at it, if I had to stick around that long to maintain my code, the disaster would already have occurred.

 

[isladogs]

If you really care about anyone using your database after 2099, change '20' to Left(Year(Date()),2).
Disaster successfully averted until the year 9999 ends.

 

[theDBguy]

Hi, just to be on the safe side, i used string functions and tailored date in the desired format. Thanks anyway for your input.

Just curious, did you try my suggestion?

 

[Pat Hartman]

Not my fault your the idiot storing a date as string. In my databases dates are stored as dates.

You probably weren't around for the Y2K debacle that took place in the late 90's when people discovered two looming problems:
1. 00 was going to be less than 99 so sorting and comparing would break since most people were not using RDBMS' at the time even though a couple were available. Dates were always stored as strings = yymmdd OR yyyymmdd.
2. Most programmers didn't implement all thee rules for leap year. Everybody got the four year rule right. Some got the 100 year rule implemented but very few got the 400 year rule which made 2000 a leap year or an exception to the exception

I'm happy to say that I NEVER, EVER caused a Y2K problem in any database I ever designed because back in 1973, I was responsible for designing a mortgage application and mortgages had a length of as many as 30 years which means that if you tested the margins as you always should, you would have found both the sorting and leap year issues.

 

[Sun_Force]

your the idiot storing a date as string

I really didn't expect this. Specially from you.
More than been surprised, I think I'm shocked.

I leave it to admins to see if it's against the rules or not, but you don't know the story behind the problem.
There may have been a good reason.
One, what @Pat Hartman explained.
Two, OP may have been assigned to manage a database designed by some one else.
or there may even be more reasonable background to this problem.

 

[MajP]

My problem is when someone has garbage and you provide them a working solution, instead of Thanks you get a complaint that your solution does not satisfy all possibilities that their garbage presents.

 

[Sun_Force]

My problem is ......

I think your problem is you look down at others just because you know better and you are too good in databases and programming.
But I think humanity is something beyond that.

 

[MajP]

No that is not my problem. When I ask a question on this thread and someone takes the time to respond, I always say thank you and if it does not solve the problem I explain in more detail. I do not criticize a solution that someone spends the time to post.

 

[MajP]

If someone provided me code that I felt did not cover all boundary conditions, I still would thank them and ask how to modify it or put the effort in to modify it. I would not be snarky saying the solution is not good enough.

 

[The_Doc_Man]

Ah, yes ... the wonderful Y2K problem. At the time, I was working in a company that made control systems for pipelines. Turned out we had multiple operating systems at the micro, mini, and super-mini machines. One of them potentially had the Y2K problem at the O/S level. All the others, IF they had it, had it at the application level, because they used the same general method that UNIX and DOS used - a "time elapsed from reference date" method.