Converting Date from a text field in Oracle

[Guest]

I am pulling a date from an Oracle database that is formatted as text and the
length being 6 characters. The date in Oracle is also in yymmdd order. I
have re-formatted the date in Access, but when I try to select between dates
or greater/less than a date, nothing will show. I have tried other ways of
formatting the date, but to no avail. Any suggestions will eb greatly
appreciated. Following is the sql:
SELECT Format(Mid([CONNDATE],3,2) & "/" & Right([CONNDATE],2) & "/" &
Left([CONNDATE],2),"Short Date") AS Outdate3
FROM CAV_LOCATIONDETL;

 

[Guest]

Your formula is bringing up a text date. Use this --
DateSerial(Left([CONNDATE],2),Right(Left([CONNDATE],4),2),Right([CONNDATE],2))

 

[Guest]

Karl, Thank you for your help. The formula you suggested gives me a new date
all together. An example is a date stored as 710118, which is actually
01/18/71, returns as 08/10/2002 with the formula you suggested. Any other
suggestion? Your help is appreciated.

Your formula is bringing up a text date. Use this --
DateSerial(Left([CONNDATE],2),Right(Left([CONNDATE],4),2),Right([CONNDATE],2))

I am pulling a date from an Oracle database that is formatted as text and the
length being 6 characters. The date in Oracle is also in yymmdd order. I
have re-formatted the date in Access, but when I try to select between dates
or greater/less than a date, nothing will show. I have tried other ways of
formatting the date, but to no avail. Any suggestions will eb greatly
appreciated. Following is the sql:
SELECT Format(Mid([CONNDATE],3,2) & "/" & Right([CONNDATE],2) & "/" &
Left([CONNDATE],2),"Short Date") AS Outdate3
FROM CAV_LOCATIONDETL;

 

[Guest]

CDate(Left("710118",2) & "/" & mid("710118",3,2)& "/" & right("710118",2))
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Karl, Thank you for your help. The formula you suggested gives me a new date
all together. An example is a date stored as 710118, which is actually
01/18/71, returns as 08/10/2002 with the formula you suggested. Any other
suggestion? Your help is appreciated.

Your formula is bringing up a text date. Use this --
DateSerial(Left([CONNDATE],2),Right(Left([CONNDATE],4),2),Right([CONNDATE],2))

I am pulling a date from an Oracle database that is formatted as text and the
length being 6 characters. The date in Oracle is also in yymmdd order. I
have re-formatted the date in Access, but when I try to select between dates
or greater/less than a date, nothing will show. I have tried other ways of
formatting the date, but to no avail. Any suggestions will eb greatly
appreciated. Following is the sql:
SELECT Format(Mid([CONNDATE],3,2) & "/" & Right([CONNDATE],2) & "/" &
Left([CONNDATE],2),"Short Date") AS Outdate3
FROM CAV_LOCATIONDETL;

 

[Guest]

Jerry, Thank you for the help. This work unless the first character is a 0
and the second character is greater than 0. If the text date is stored as
030407, it returns 3/4/2007, instead of 4/7/2003. If the stored text date is
000407, it returns 4/7/2000 as it should. Thanks again.

CDate(Left("710118",2) & "/" & mid("710118",3,2)& "/" & right("710118",2))
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Karl, Thank you for your help. The formula you suggested gives me a new date
all together. An example is a date stored as 710118, which is actually
01/18/71, returns as 08/10/2002 with the formula you suggested. Any other
suggestion? Your help is appreciated.

Your formula is bringing up a text date. Use this --
DateSerial(Left([CONNDATE],2),Right(Left([CONNDATE],4),2),Right([CONNDATE],2))

:

I am pulling a date from an Oracle database that is formatted as text and the
length being 6 characters. The date in Oracle is also in yymmdd order. I
have re-formatted the date in Access, but when I try to select between dates
or greater/less than a date, nothing will show. I have tried other ways of
formatting the date, but to no avail. Any suggestions will eb greatly
appreciated. Following is the sql:
SELECT Format(Mid([CONNDATE],3,2) & "/" & Right([CONNDATE],2) & "/" &
Left([CONNDATE],2),"Short Date") AS Outdate3
FROM CAV_LOCATIONDETL;

 

[Guest]

Try concatenating "20" to the date like this -
DateSerial(Left("20"&[CONNDATE],4),Right(Left("20"&[CONNDATE],6),2),Right([CONNDATE],2))

Try doing the same with what Jerry Whittle wrote.

Jerry, Thank you for the help. This work unless the first character is a 0
and the second character is greater than 0. If the text date is stored as
030407, it returns 3/4/2007, instead of 4/7/2003. If the stored text date is
000407, it returns 4/7/2000 as it should. Thanks again.

CDate(Left("710118",2) & "/" & mid("710118",3,2)& "/" & right("710118",2))
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Karl, Thank you for your help. The formula you suggested gives me a new date
all together. An example is a date stored as 710118, which is actually
01/18/71, returns as 08/10/2002 with the formula you suggested. Any other
suggestion? Your help is appreciated.

:

Your formula is bringing up a text date. Use this --
DateSerial(Left([CONNDATE],2),Right(Left([CONNDATE],4),2),Right([CONNDATE],2))

:

I am pulling a date from an Oracle database that is formatted as text and the
length being 6 characters. The date in Oracle is also in yymmdd order. I
have re-formatted the date in Access, but when I try to select between dates
or greater/less than a date, nothing will show. I have tried other ways of
formatting the date, but to no avail. Any suggestions will eb greatly
appreciated. Following is the sql:
SELECT Format(Mid([CONNDATE],3,2) & "/" & Right([CONNDATE],2) & "/" &
Left([CONNDATE],2),"Short Date") AS Outdate3
FROM CAV_LOCATIONDETL;

 

[Guest]

Karl,

That worked!! For both your suggestion and Jerry's suggestion. Thank you
both for your input and helping me with this problem. It is very much
appreciated.

Try concatenating "20" to the date like this --
DateSerial(Left("20"&[CONNDATE],4),Right(Left("20"&[CONNDATE],6),2),Right([CONNDATE],2))

Try doing the same with what Jerry Whittle wrote.

Jerry, Thank you for the help. This work unless the first character is a 0
and the second character is greater than 0. If the text date is stored as
030407, it returns 3/4/2007, instead of 4/7/2003. If the stored text date is
000407, it returns 4/7/2000 as it should. Thanks again.

CDate(Left("710118",2) & "/" & mid("710118",3,2)& "/" & right("710118",2))
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Karl, Thank you for your help. The formula you suggested gives me a new date
all together. An example is a date stored as 710118, which is actually
01/18/71, returns as 08/10/2002 with the formula you suggested. Any other
suggestion? Your help is appreciated.

:

Your formula is bringing up a text date. Use this --
DateSerial(Left([CONNDATE],2),Right(Left([CONNDATE],4),2),Right([CONNDATE],2))

:

I am pulling a date from an Oracle database that is formatted as text and the
length being 6 characters. The date in Oracle is also in yymmdd order. I
have re-formatted the date in Access, but when I try to select between dates
or greater/less than a date, nothing will show. I have tried other ways of
formatting the date, but to no avail. Any suggestions will eb greatly
appreciated. Following is the sql:
SELECT Format(Mid([CONNDATE],3,2) & "/" & Right([CONNDATE],2) & "/" &
Left([CONNDATE],2),"Short Date") AS Outdate3
FROM CAV_LOCATIONDETL;