Hi

I have a field Delivery_date under "DateTime" format.

I need to update it with a calculated variable (mdel_date) which shows up as 38341.6703819

How do I write the Update / Set command line?

Thanks

you have a date calculation which produces 38341.6703819 as a datetime value?

what coding scheme is that?

if 38341 is number of days, then relative to what date?

is 6703819 the number of microseconds after midnight? picoseconds? femtoseconds?

Hi there!

I haven't got a clue. But I found the answer:

The answer was '#dateformat(session.mdest_ETA,'yyyy-mm-dd')#',

and you will of course have recognised ColdFusion gobbledeegook

Thank you for your interest.

Have a nice day

you know, that's not really fair, posting a coldfusion question in the mysql forum

you should have posted it in the coldfusion forum

as it was, i spent time searching the mysql documentation for those formats and came up empty

thank you for your consideration

you have a nice day

Hi Rudy

I don't mean to start an argument here but my question was indeed a MySQL related question - "Update Orders set Date_Arrival = (what?)" as was every other database related question I posted on this forum in the past - although they all were integrated into ColdFusion tags.

CF has decent forums but the place for database related issues is here and I didn't know - and I still don't know for sure - that the value I got for the date 38341.6703819 was purely coldfusion - and I still doubt it was a purely Coldfusion one. i.e. HSBC have an online payment system which uses this format too...

So I hope no offense was caused and - yes - I did a lot of searching and experimenting before I posted my question.

So you too have a great day

to answer your question -- "Update Orders set Date_Arrival = (what?)"

the (what?) has to be a date literal

this may be in one of several formats --
- a string 'YYYY-MM-DD '
- a string 'YY-MM-DD'
- a string 'YYYYMMDD'
- a string 'YYMMDD'
- a number YYYYMMDD
- a number YYMMDD

okay?

Yes "okay"

And thank you

FYI, for anybody that cares , that's the current date value using 1/1/1900 12:00:00AM as a starting point. The fractional part is based on an 86,400-second day. 38,341 days and 57,921 seconds later equates to 12/20/2004 4:05:21PM. You can use 2 functions in VB[A/Script/whatever] to transfer back and forth, CDate() And CDbl().

um, billdo75, where would VB[A/Script/whatever] come into play in a scenario where the web page is written in coldfusion and the database is mysql?

That was just the first example that popped into my head. I don't do any web design, so I can't really comment on Coldfusion, but as far as MySql, you can cast your date back and forth:

SELECT CAST(38341.67 AS datetime)
SELECT CAST(Now() AS float)

no, i'm sorry, that does not work the way you think it will

in mysql, SELECT CAST(38341.6703819 AS datetime) gives 3834-01-03 19:00:00