Date() update format

  • Thread starter Thread starter RitchieJHicks
  • Start date Start date
R

RitchieJHicks

I have an append query which I run to update a field in all my records to
date(). However, this does not give the format required by the web database
that I upload the final data to.

The database (in MyPhp admin) requires the uploaded date format to be:
yyyy-mm-dd 00:00:00

My question is, what do I need to do the the date() critieria in the query
to make it produce the format above? I have tried to use the Now() criteria,
but that only gives me the format dd/mm/yyyy 00:00.

Thanks.



-------------------------
 
Since dates are stored as a number of days (and partial days) from the zero
date of Dec 30 1899 you can either use a query as the source for the Web
database and apply a format to get the string to appear in the format you want
Field: MyDateString: Format([MyDate],"yyyy-mm-dd hh:mm:ss")

Or you can make the field where you store the date a text field and use the
format in your update query.

UPDATE MyTable
SET MyDateStringField = Format(Date(),"yyyy-mm-dd hh:mm:ss")


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
I'm sorry, but I'm a bit dim about these things. Where would I put that code
- I don't understand!? Sorry....
--
-----------------------


John Spencer said:
Since dates are stored as a number of days (and partial days) from the zero
date of Dec 30 1899 you can either use a query as the source for the Web
database and apply a format to get the string to appear in the format you want
Field: MyDateString: Format([MyDate],"yyyy-mm-dd hh:mm:ss")

Or you can make the field where you store the date a text field and use the
format in your update query.

UPDATE MyTable
SET MyDateStringField = Format(Date(),"yyyy-mm-dd hh:mm:ss")


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
I have an append query which I run to update a field in all my records to
date(). However, this does not give the format required by the web database
that I upload the final data to.

The database (in MyPhp admin) requires the uploaded date format to be:
yyyy-mm-dd 00:00:00

My question is, what do I need to do the the date() critieria in the query
to make it produce the format above? I have tried to use the Now() criteria,
but that only gives me the format dd/mm/yyyy 00:00.

Thanks.



-------------------------
Click to expand...
Click to expand...
 
Which bit of code are you talking about?

If you are talking about the update query.

Add a text field to your table to hold the text version of the date
In your update query
Add the new field
Set the update "block" to
Format(Date(),"yyyy-mm-dd hh:mm:ss")

Field: [Your New Field]
Update To: Format(Date(),"yyyy-mm-dd hh:mm:ss")

If you are going to use a query as the source instead of the table then you
would need to add all the fields you are currently using minus the date field
which you would replace with a calculated field that used the expression
Format([Your Date Field],"yyyy-mm-dd hh:mm:ss")

Field: [Your Date Field]: Format([Your Table Name].[Your Date
Field],"yyyy-mm-dd hh:mm:ss")


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
I have an append query which I run to update a field in all my records to
date(). However, this does not give the format required by the web database
that I upload the final data to.

The database (in MyPhp admin) requires the uploaded date format to be:
yyyy-mm-dd 00:00:00

My question is, what do I need to do the the date() critieria in the query
to make it produce the format above? I have tried to use the Now() criteria,
but that only gives me the format dd/mm/yyyy 00:00.

Thanks.



-------------------------
Click to expand...

Don't confuse data STORAGE with data FORMATTING. They are different!

The date is stored in your table as a Double Float number, a count of days
since (or before) 12/30/1899 00:00:00. You can format it any way you like.

In order to export a date value from an Access table in your format, use a
Query based on the table, and include a calculated field:

ExpDate: Format(Date(), "yyyy-mm-dd hh:nn:ss")
 
Done, brilliant. You're a little star.
--
-----------------------


John Spencer said:
Which bit of code are you talking about?

If you are talking about the update query.

Add a text field to your table to hold the text version of the date
In your update query
Add the new field
Set the update "block" to
Format(Date(),"yyyy-mm-dd hh:mm:ss")

Field: [Your New Field]
Update To: Format(Date(),"yyyy-mm-dd hh:mm:ss")

If you are going to use a query as the source instead of the table then you
would need to add all the fields you are currently using minus the date field
which you would replace with a calculated field that used the expression
Format([Your Date Field],"yyyy-mm-dd hh:mm:ss")

Field: [Your Date Field]: Format([Your Table Name].[Your Date
Field],"yyyy-mm-dd hh:mm:ss")


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
I'm sorry, but I'm a bit dim about these things. Where would I put that code
- I don't understand!? Sorry....
Click to expand...
Click to expand...
 
Back
Top