Convert a text string to a date? 1

[mmck1964]

I have a database where the date is a text string.
[Date] [Would like it to be]
112904 11/29/2004
91807 09/18/2007
12206 01/22/2006
101305 10/13/2005
1/15/98 01/15/1998

I am trying to do this in a query.
Thanks!

 

[lespaul]

mid, left and right are the function's you'll need:

SELECT LEFT([Date], 2) & "/" & Mid([Date], 3, 2) & "/" & Right([Date], 4) As FormattedDateString FROM tableName

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[SkipVought]

Hi,

Looks as if you'll need more logic in your conversion.

1) you have a mixture of data with and without slashes.

2) you have strings without slashes that are 5 or 6 bytes, so the left 2 is not always the month

3) you have mixed implied centuries.

are you certain that you have posted examples that are completely representative of the various strings?

Skip,
[sub]
[red][/red]
[/sub]

 

[SkipVought]

From the examples that you have posted...
[tt]
dateSerial(iif(right(right(replace([Date],"/","")&"0",6),2)<"20","20","19")
&right(right(replace([Date],"/","")&"0",6),2),left(right(replace([Date],"/"
,"")&"0",6),2),mid(right(replace([Date],"/","")&"0",6),3,2))
[/tt]

Skip,
[sub]
[red][/red]
[/sub]

 

[traingamer]

Which still can't determine if 111995 is 1/1/1995 or 11/19/95...

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[SkipVought]

Given the posting examples, I would MOST STRONGLY doubt if 111995 were 1/1/1995. The evidence seems to indicate that the last 2 characters are intended to be YEAR. Moreover, the evidence seems to indicate that the unslashed format is either of these...
[tt]
mddyy
mmddyy
[/tt]
hence the left pad of ZERO to a length of 6.

Skip,
[sub]
[red][/red]
[/sub]

 

[lespaul]

My bad, it was so early in the morning I didn't even notice the differing formats in the example data!


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[traingamer]

Given the posting examples, I would MOST STRONGLY doubt if 111995 were 1/1/1995.

Most likely true, but there is the possible ambiguity of 12206 being 12/2/06 or 1/22/06. (Not really enough info in the examples to be absolutely sure).

In any case, a very good solution to the original question.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[SkipVought]

...which I why I originally stated, "are you certain that you have posted examples that are completely representative of the various strings?"

And how would the OP know whether, 12206 was intended to be 12/2/06 or 1/22/06, without more information other than data examples?

Skip,
[sub]
[red][/red]
[/sub]

 

[mmck1964]

The examples posted are correct. The data did not have any leading zero's. Left to Right is always; first 2 are the year, next 2 are the day and last 1 or 2 are the month.
Thanks for all of the inout.

 

[lespaul]

first 2 are the year, next 2 are the day and last 1 or 2 are the month.

Really?

[tt]
[Date] [Would like it to be]
112904 11/29/2004
91807 09/18/2007
12206 01/22/2006
101305 10/13/2005
1/15/98 01/15/1998[/tt]

then the example in red and bolded above would be:

first two year: 91
next two day : 80
next one or two month: 7
7/80/91, but you say 9/18/07

blue and bold would be:
year: 12
day: 20
month: 6
6/20/12, but you claim it should be 1/22/06


and you last example isn't formatted like the others at all...

 

[mmck1964]

My bad!! I should have said "right to left"

91807 would be 9/18/07
122206 would be 12/22/06

I would be happy with getting the data to look like 9/18/07 and 12/22/06. I really do not need it to say 2006 or 09.
Thanks!

 

[SkipVought]

So what is the current situation? Have your questions been answered?

Skip,
[sub]
[red][/red]
[/sub]

 

[mmck1964]

No. I tried
SELECT ILOCNOW_CSV(IIf(Right(Right(Replace([f4],"/","") & "0",6),2)<"20","20","19") & Right(Right(Replace([f4],"/","") & "0",6),2),Left(Right(Replace([f4],"/","") & "0",6),2),Mid(Right(Replace([f4],"/","") & "0",6),3,2)) AS Expr1
FROM ILOCNOW_CSV;

ILOCNOW_CSV is the table and [f4] is the field with the data

I get a "Undefined Function 'ILOCNOW_CSV' in expression" error

 

[SkipVought]

SELECT ILOCNOW_CSV[B][RED]
,[/RED][/B] (IIf(Right(Right(Replace([f4],"/","") & "0",6),2)<"20","20","19") & Right(Right(Replace([f4],"/","") & "0",6),2),Left(Right(Replace([f4],"/","") & "0",6),2),Mid(Right(Replace([f4],"/","") & "0",6),3,2)) AS Expr1

Skip,
[sub]
[red][/red]
[/sub]

 

[mmck1964]

Now I get the error "Syntax Error (Comma)in query expression, and when I click OK, the curser goes between )) and AS like the problem is there???

 

[SkipVought]

I wan not paying attention to the parentheses...

SELECT ILOCNOW_CSV
, dateSerial(iif(right(right(replace([Date],"/","")&"0",6),2)<"20","20","19")
&right(right(replace([Date],"/","")&"0",6),2),left(right(replace([Date],"/"
,"")&"0",6),2),mid(right(replace([Date],"/","")&"0",6),3,2)) AS Expr1

Skip,
[sub]
[red][/red]
[/sub]

 

[mmck1964]

Now I do get dates, but is DateSerial what I need? from
112904 I get 2/28/1941
50306 I get 3/1/1964

 

[SkipVought]

Sorry,

DateSerial(IIF(RIGHT(RIGHT("0"&SUBSTITUTE([Date],"/",""),6),2)<"20","20",
"19")&RIGHT(RIGHT("0"&SUBSTITUTE([Date],"/",""),6),2),LEFT(RIGHT
("0"&SUBSTITUTE([Date],"/",""),6),2),MID(RIGHT("0"&SUBSTITUTE([Date],"/",
""),6),3,2))

Skip,
[sub]
[red][/red]
[/sub]

 

[mmck1964]

I hate to seem so stupid and I do appreciate your help, but now I get a "Undefined function 'SUBSTITUTE' in expression