date conversion

[LG]

I tried this the other day and it worked today i brought in another table and
I am getting incorrect data.
The original was 20080229
I tried this
FixDate: DateSerial(Left([submted],4),Mid([submted],5,2),Right([submted],2))
and I got 05/08/2022
Than I tried
FixDate:
IIf(IsDate(Format([submted],"@@@@-@@-@@")),CDate(Format([submted],"@@@@-@@-@@")),Null)
and I got 08/20/229
The result I am looking for is 02/29/2008
Thanks

 

[S.Clark]

Today is 10/8/2009. I did the following in the Immediate window
? format(date(), "@@@@-@@-@@")
10/8-/2-009

I think you want:
format(date(), "yyyy-mm-dd")
2009-10-08

 

[LG]

The date I was using are old dates from files. so the date is correct of
02/29/2008.
How would I get that date?

Today is 10/8/2009. I did the following in the Immediate window
? format(date(), "@@@@-@@-@@")
10/8-/2-009

I think you want:
format(date(), "yyyy-mm-dd")
2009-10-08

I tried this the other day and it worked today i brought in another table and
I am getting incorrect data.
The original was 20080229
I tried this
FixDate: DateSerial(Left([submted],4),Mid([submted],5,2),Right([submted],2))
and I got 05/08/2022
Than I tried
FixDate:
IIf(IsDate(Format([submted],"@@@@-@@-@@")),CDate(Format([submted],"@@@@-@@-@@")),Null)
and I got 08/20/229
The result I am looking for is 02/29/2008
Thanks

 

[KARL DEWEY]

Try this --
FixDate:
DateSerial(Left([submted],4),Right(Left([submted],6),2),Right(Left([submted],8),2))


--
Build a little, test a little.

The date I was using are old dates from files. so the date is correct of
02/29/2008.
How would I get that date?

Today is 10/8/2009. I did the following in the Immediate window
? format(date(), "@@@@-@@-@@")
10/8-/2-009

I think you want:
format(date(), "yyyy-mm-dd")
2009-10-08

I tried this the other day and it worked today i brought in another table and
I am getting incorrect data.
The original was 20080229
I tried this
FixDate: DateSerial(Left([submted],4),Mid([submted],5,2),Right([submted],2))
and I got 05/08/2022
Than I tried
FixDate:
IIf(IsDate(Format([submted],"@@@@-@@-@@")),CDate(Format([submted],"@@@@-@@-@@")),Null)
and I got 08/20/229
The result I am looking for is 02/29/2008
Thanks

 

[LG]

The date I get now is 02/08/2020
I will also have another field that needs the same conversion:
The 2 fields are submted and dmrdtercv.

Try this --
FixDate:
DateSerial(Left([submted],4),Right(Left([submted],6),2),Right(Left([submted],8),2))


--
Build a little, test a little.

The date I was using are old dates from files. so the date is correct of
02/29/2008.
How would I get that date?

Today is 10/8/2009. I did the following in the Immediate window
? format(date(), "@@@@-@@-@@")
10/8-/2-009

I think you want:
format(date(), "yyyy-mm-dd")
2009-10-08

:

I tried this the other day and it worked today i brought in another table and
I am getting incorrect data.
The original was 20080229
I tried this
FixDate: DateSerial(Left([submted],4),Mid([submted],5,2),Right([submted],2))
and I got 05/08/2022
Than I tried
FixDate:
IIf(IsDate(Format([submted],"@@@@-@@-@@")),CDate(Format([submted],"@@@@-@@-@@")),Null)
and I got 08/20/229
The result I am looking for is 02/29/2008
Thanks

 

[John W. Vinson]

I tried this the other day and it worked today i brought in another table and
I am getting incorrect data.
The original was 20080229
I tried this
FixDate: DateSerial(Left([submted],4),Mid([submted],5,2),Right([submted],2))
and I got 05/08/2022
Than I tried
FixDate:
IIf(IsDate(Format([submted],"@@@@-@@-@@")),CDate(Format([submted],"@@@@-@@-@@")),Null)
and I got 08/20/229
The result I am looking for is 02/29/2008
Thanks

That's very odd. What is the datatype of the 20080229 field? Text, number?
Does it have any Format or Input Mask defined which might be changing the
display to something other than what is stored?

 

[LG]

The field data type is text with no format or input mask.

I tried this the other day and it worked today i brought in another table and
I am getting incorrect data.
The original was 20080229
I tried this
FixDate: DateSerial(Left([submted],4),Mid([submted],5,2),Right([submted],2))
and I got 05/08/2022
Than I tried
FixDate:
IIf(IsDate(Format([submted],"@@@@-@@-@@")),CDate(Format([submted],"@@@@-@@-@@")),Null)
and I got 08/20/229
The result I am looking for is 02/29/2008
Thanks

That's very odd. What is the datatype of the 20080229 field? Text, number?
Does it have any Format or Input Mask defined which might be changing the
display to something other than what is stored?

 

[John W. Vinson]

The field data type is text with no format or input mask.

I tried this the other day and it worked today i brought in another table and
I am getting incorrect data.
The original was 20080229
I tried this
FixDate: DateSerial(Left([submted],4),Mid([submted],5,2),Right([submted],2))
and I got 05/08/2022
Than I tried
FixDate:
IIf(IsDate(Format([submted],"@@@@-@@-@@")),CDate(Format([submted],"@@@@-@@-@@")),Null)
and I got 08/20/229
The result I am looking for is 02/29/2008
Thanks

That's very odd. What is the datatype of the 20080229 field? Text, number?
Does it have any Format or Input Mask defined which might be changing the
display to something other than what is stored?

Doublecheck - or redo - your expressions. Either one should work and should
give the correct date! Here's from my testing in the Immediate window:

?DateSerial(Left("20080229",4),Mid("20080229",5,2),Right("20080229",2))
2/29/2008
?IIf(IsDate(Format("20080229","@@@@-@@-@@")),CDate(Format("20080229","@@@@-@@-@@")),Null)
2/29/2008



Also doublecheck the data in the table (for leading or trailing blanks,
extraneous characters, etc.); and you may want to Compact and Repair your
database (make a backup) and possibly Decompile and then compile your code,
there might be some sort of corruption causing the problem.

 

[LG]

that was it on the import it added a leading space.
Thank you

The field data type is text with no format or input mask.

I tried this the other day and it worked today i brought in another table and
I am getting incorrect data.
The original was 20080229
I tried this
FixDate: DateSerial(Left([submted],4),Mid([submted],5,2),Right([submted],2))
and I got 05/08/2022
Than I tried
FixDate:
IIf(IsDate(Format([submted],"@@@@-@@-@@")),CDate(Format([submted],"@@@@-@@-@@")),Null)
and I got 08/20/229
The result I am looking for is 02/29/2008
Thanks



That's very odd. What is the datatype of the 20080229 field? Text, number?
Does it have any Format or Input Mask defined which might be changing the
display to something other than what is stored?

Doublecheck - or redo - your expressions. Either one should work and should
give the correct date! Here's from my testing in the Immediate window:

?DateSerial(Left("20080229",4),Mid("20080229",5,2),Right("20080229",2))
2/29/2008
?IIf(IsDate(Format("20080229","@@@@-@@-@@")),CDate(Format("20080229","@@@@-@@-@@")),Null)
2/29/2008



Also doublecheck the data in the table (for leading or trailing blanks,
extraneous characters, etc.); and you may want to Compact and Repair your
database (make a backup) and possibly Decompile and then compile your code,
there might be some sort of corruption causing the problem.