I have two columns. ColA and ColB contains char(10) with data "20090520" and "20090521".
I want to select and get the date difference in days. I have tried using Format() and CDate() but MS Access always display as #ERROR.
+1
A:
Try using DateSerial() to convert the dates:
DateSerial(Left([FieldName],4),Mid([FieldName],5,2),Right([FieldName],2))
Gary.Ray
2009-05-21 14:08:03
+3
A:
Access prefers its dates in this format:
#2009-12-01#
You can convert your date to something Access understands with:
CDate(Format([ColA], "0000-00-00"))
Or alternatively:
DateSerial(Left([ColA],4),Mid([ColA],5,2),Right([ColA],2))
And to display the result in your preferred format:
Format(<date here>, "dd-mm-yyyy")
Andomar
2009-05-21 14:08:09
Thanks. This did the trick.
2009-05-21 14:21:02
+1 for preferred date - mm/dd/yy will also work, but a version of year, month, day is the least ambiguous.Dates are stored as numbers and displayed in format set by Windows Regional and Language Settings, but you can override that with Format, if you wish. The disadvantage of using the Format function, rather than the regional setting, is that Format returns a string, not a date.
Remou
2009-05-21 20:13:46
A:
If at all possible, change the datatype to a date datatype. You should not store dates as character data.
HLGEM
2009-05-21 16:40:21
It wasn't clear to me if the OP was doing that, but it was a question that occurred to me.
David-W-Fenton
2009-05-21 20:49:15
A:
I am connecting to another database which I have no control on. That is why this problem occurred. Thanks for the feedback.
Krimmel
2009-05-22 01:49:46