Results 1 to 11 of 11

Thread: Date format

  1. #1
    Join Date
    Sep 2004
    Posts
    39

    Unanswered: Date format

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2004
    Posts
    39
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2004
    Posts
    39
    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

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2004
    Posts
    39
    Yes "okay"

    And thank you

  8. #8
    Join Date
    Jan 2004
    Location
    Ft Collins, CO
    Posts
    22
    Quote Originally Posted by r937
    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?
    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().

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jan 2004
    Location
    Ft Collins, CO
    Posts
    22
    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)

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •