Convert text to date

[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))

 

[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?

 

[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.

 

[Leo_Polla_Psemata]

Just curious, did you try my suggestion?

Hi, yes i did, however, the "text" that holds the date is 211130 , yymmdd, by using CDate(Format([TextField], "00-00-00")) , i cannot convince the system which two digits relate to yy. I don't want to change my whole system settings as this might affect other sections, anyway, by using the string functions, i twisted the text and now it is just fine.

 

[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 Majp
Just to give some more info, i download tables from our business system, then i process and automate several tasks in my daily activities at the office. I need to use this in a query field so the above function, is way to advance for a casual user as I am.

I made the below and works.

SO1: Format(DLookUp("(Mid([txt],95,2))&'/'&(Mid([txt],93,2))&'/'&(Mid([txt],91,2))","txt1","[bl]='" & [bl] & "' and [Lin1]='12' and [ot]='1'"),"dd/mmm/yyyy")

Anyway, many thanks for your time and input.