date conversion

  • Thread starter Thread starter LG
  • Start date Start date
L

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

S.Clark said:
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 said:
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
Click to expand...
Click to expand...
 
Try this --
FixDate:
DateSerial(Left([submted],4),Right(Left([submted],6),2),Right(Left([submted],8),2))


--
Build a little, test a little.


LG said:
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?

S.Clark said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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.
KARL DEWEY said:
Try this --
FixDate:
DateSerial(Left([submted],4),Right(Left([submted],6),2),Right(Left([submted],8),2))


--
Build a little, test a little.


LG said:
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?

S.Clark said:
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
Click to expand...
Click to expand...
Click to expand...
 
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
Click to expand...

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?
 
The field data type is text with no format or input mask.

John W. Vinson said:
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
Click to expand...

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?
Click to expand...
 
The field data type is text with no format or input mask.

John W. Vinson said:
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
Click to expand...

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?
Click to expand...
Click to expand...

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.
 
that was it on the import it added a leading space.
Thank you

John W. Vinson said:
The field data type is text with no format or input mask.

John W. Vinson said:
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?
Click to expand...
Click to expand...

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.
Click to expand...
 
Back
Top