By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,930 Members | 1,383 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,930 IT Pros & Developers. It's quick & easy.

How to convert Text dates to Date format?

P: n/a
After importing text date fields, the dates look like this:

12/31/2003 8:00:00 AM

I'm having trouble working with these dates with Date(), Now(), etc, --
does not seem to match these dates.
I've tried converting them in a query like this:

SELECT CDate([ApptDate]) AS dtmApptDate
FROM tblAppointments

But that does not seem to help...
I'm only interested in the "date" part of the date (e.g. 12/31/03) and don't
need the time. Is there some simple text-to-date conversion I can perform
to discard all but the date portion?

Thanks in advance...
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
How about this ...

SELECT Format(CDate([ApptDate]), "mm/dd/yyyy") FROM ...

or if ApptDate is already a valid date, just use the FORMAT function
without the CDate() function at all

--

Danny J. Lesandrini
dl*********@hotmail.com
http://amazecreations.com
"deko" <dj****@hotmail.com> wrote in message news:Y%*******************@newssvr29.news.prodigy. com...
After importing text date fields, the dates look like this:

12/31/2003 8:00:00 AM

I'm having trouble working with these dates with Date(), Now(), etc, --
does not seem to match these dates.
I've tried converting them in a query like this:

SELECT CDate([ApptDate]) AS dtmApptDate
FROM tblAppointments

But that does not seem to help...
I'm only interested in the "date" part of the date (e.g. 12/31/03) and don't
need the time. Is there some simple text-to-date conversion I can perform
to discard all but the date portion?

Thanks in advance...

Nov 12 '05 #2

P: n/a
Try
Format(CDate(ApptDate), "Medium Date")

Phil

"deko" <dj****@hotmail.com> wrote in message
news:Y%*******************@newssvr29.news.prodigy. com...
After importing text date fields, the dates look like this:

12/31/2003 8:00:00 AM

I'm having trouble working with these dates with Date(), Now(), etc, --
does not seem to match these dates.
I've tried converting them in a query like this:

SELECT CDate([ApptDate]) AS dtmApptDate
FROM tblAppointments

But that does not seem to help...
I'm only interested in the "date" part of the date (e.g. 12/31/03) and don't need the time. Is there some simple text-to-date conversion I can perform
to discard all but the date portion?

Thanks in advance...

Nov 12 '05 #3

P: n/a
Thanks Danny and Pil for the replies!

I'm still experimenting with the FORMAT trick, but also found this to be
helpful when doing the import:

rst!ApptDate = DateValue(varAppt.Start)

so far so good...

"deko" <dj****@hotmail.com> wrote in message
news:Y%*******************@newssvr29.news.prodigy. com...
After importing text date fields, the dates look like this:

12/31/2003 8:00:00 AM

I'm having trouble working with these dates with Date(), Now(), etc, --
does not seem to match these dates.
I've tried converting them in a query like this:

SELECT CDate([ApptDate]) AS dtmApptDate
FROM tblAppointments

But that does not seem to help...
I'm only interested in the "date" part of the date (e.g. 12/31/03) and don't need the time. Is there some simple text-to-date conversion I can perform
to discard all but the date portion?

Thanks in advance...

Nov 12 '05 #4

P: n/a
sorry, Phil...

"deko" <dj****@hotmail.com> wrote in message
news:wS*******************@newssvr25.news.prodigy. com...
Thanks Danny and Pil for the replies!

I'm still experimenting with the FORMAT trick, but also found this to be
helpful when doing the import:

rst!ApptDate = DateValue(varAppt.Start)

so far so good...

"deko" <dj****@hotmail.com> wrote in message
news:Y%*******************@newssvr29.news.prodigy. com...
After importing text date fields, the dates look like this:

12/31/2003 8:00:00 AM

I'm having trouble working with these dates with Date(), Now(), etc, --
does not seem to match these dates.
I've tried converting them in a query like this:

SELECT CDate([ApptDate]) AS dtmApptDate
FROM tblAppointments

But that does not seem to help...
I'm only interested in the "date" part of the date (e.g. 12/31/03) and

don't
need the time. Is there some simple text-to-date conversion I can perform to discard all but the date portion?

Thanks in advance...


Nov 12 '05 #5

P: n/a
I ended up doing the conversion on import like this:

rst.AddNew
rst!ApptDate = DateValue(CDate(varAppt.Start))
rst.Update

seems to work nicely
"Chuck Grimsby" <c.*******@worldnet.att.net.invalid> wrote in message
news:4n********************************@4ax.com...

Personally, I'd use CVDate rather then CDate. CVDate handles "bad"
dates more often.

But then again, of late I've been using DateSerial (and TimeSerial
when needed) for conversions, so I can more accurately control the
conversion.
On Mon, 8 Dec 2003 08:51:19 -0700, "Danny J. Lesandrini"
<dl*********@hotmail.com> wrote:
How about this ...
SELECT Format(CDate([ApptDate]), "mm/dd/yyyy") FROM ...
or if ApptDate is already a valid date, just use the FORMAT function
without the CDate() function at all

--
Why Do 24-hour Stores Have Locks On The Doors?

Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.