Text to Date

  • Thread starter Thread starter rama
  • Start date Start date
R

rama

I am importing some text data to access 2007 database which contains
few date fields. I tried to convert this date stored as text to date
using following expression in access but it brings unexpected
results.
ReqDate: DateSerial(Right([RequirementDate],4),Left(Right
([RequirementDate],10),2),Left(Mid([RequirementDate],5),2))

I am putting some sample results of the query, some are correct and
some are wrong. I would like to eliminate these errors.

RequirementDate ReqDate
21.05.2009 9/5/2010
06.05.2008 6/5/2008
06.05.2008 6/5/2008
22.01.2009 10/1/2010
10.06.2008 10/6/2008
Thanks
Rama
 
That's a fairly weird way to store dates. I would have expected the first two
characters to be the day of the month. You didn't say if the displayed
records are what you want or if some are wrong. If some are wrong, you should
have provided the desired date:

If your ReqDate is what you want then try:

DateSerial(Right([RequirementDate],4),Left([RequirementDate],2),Mid([RequirementDate],4,2))

otherwise try:
DateSerial(Right([RequirementDate],4),Mid([RequirementDate],4,2),
Left([RequirementDate],2))


If some of your displayed records are wrong then try
 
ReqDate: CDate(Mid(RequirementDate, 4,2) &"/"& Left(RequirementDate, 2)&"/"&
Right(RequirementDate, 4))

The above will work IF (1) all the dates are valid (IE no February 30th);
(2) no null values; and (3) the field always has the same number of
characters.
 
rama said:
I am importing some text data to access 2007 database which contains
few date fields. I tried to convert this date stored as text to date
using following expression in access but it brings unexpected
results.
ReqDate: DateSerial(Right([RequirementDate],4),Left(Right
([RequirementDate],10),2),Left(Mid([RequirementDate],5),2))

I am putting some sample results of the query, some are correct and
some are wrong. I would like to eliminate these errors.

RequirementDate ReqDate
21.05.2009 9/5/2010
06.05.2008 6/5/2008
06.05.2008 6/5/2008
22.01.2009 10/1/2010
10.06.2008 10/6/2008
Click to expand...


Try using the CDate function.
 
That's a fairly weird way to store dates. I would have expected the firsttwo
characters to be the day of the month. You didn't say if the displayed
records are what you want or if some are wrong. If some are wrong, you should
have provided the desired date:

If your ReqDate is what you want then try:

DateSerial(Right([RequirementDate],4),Left([RequirementDate],2),Mid([Requir­ementDate],4,2))

otherwise try:
  DateSerial(Right([RequirementDate],4),Mid([RequirementDate],4,2),
Left([RequirementDate],2))

If some of your displayed records are wrong then try
--
Duane Hookom
Microsoft Access MVP



rama said:
I am importing some text data to access 2007 database which contains
few date fields. I tried to convert this date stored as text to date
using following expression in access but it brings unexpected
results.
ReqDate: DateSerial(Right([RequirementDate],4),Left(Right
([RequirementDate],10),2),Left(Mid([RequirementDate],5),2))
Click to expand...
I am putting some sample results of the query, some are correct and
some are wrong.  I would like to eliminate these errors.
Click to expand...
RequirementDate    ReqDate
21.05.2009 9/5/2010
06.05.2008 6/5/2008
06.05.2008 6/5/2008
22.01.2009 10/1/2010
10.06.2008 10/6/2008
Thanks
Rama
.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Thanks for the help. The second expression brought the correct
results.
DateSerial(Right([RequirementDate],4),Mid([RequirementDate],4,2),Left
([RequirementDate],2))
Rama
 
Back
Top