8 Replies Latest reply on Mar 16, 2008 1:32 PM by (Sean_DeMerchant)

    Date Picker and Formating

      I am creating a script which saves a record, then allows the user to send an email notification on a future date, the record is then deleted via cron job after 14 days from the send date.
      My problem is because the cron job uses curdate() the database date format must be yyyy-mm-dd (2008-03-14) but I would like the screen display to show as d/m/yy (14/03/08), so I have put these settings into the Date Picker dialog and set the default value to <?php echo date("d/m/y") ?> however, when I run the script it inputs 2014-03-08 into the database column instead of 2008-03-14 both the form input and mysql columns are set to data type DATE. I have tried combining yyy-mm-dd with the other display formats m/d/yy etc. but mostly they return 0000-00-00. Any help wold be greatly appreciated as I have been pulling my hair out over this for a week now.

      Thanks in advance...
        • 1. Re: Date Picker and Formating
          Level 1
          It seems if I set the screen display format to yyyy-mm-dd the date is entered into the database correctly and the "delete script" which is executed by the cron job works without a hitch, however this still leaves me with the problem that the date show on screen as yyyy-mm-dd
          (2008-03-15) which is a very untidy format to present to visitors...
          Getting desperate...

          Thanks
          • 2. Re: Date Picker and Formating
            Günter Schenk Level 4
            Hi Robert,

            I guess you´re probably just confusing the "database date and time formats" vs the "screen date and time formats" sections in the Control Panel´s "Date Formats" settings:

            a) "database date and time formats" :: MYSQL usually expects this to be yyyy-mm-dd (date format) and HH:mm:ss (time format)

            b) "screen date and time formats" :: the 2nd "Date format" option is d/m/yy -- did you try that already ?

            Cheers,
            Günter Schenk
            Adobe Community Expert, Dreamweaver
            • 3. Re: Date Picker and Formating
              Level 1
              Thanks for your reply... yes I have tried everything I can think of

              I am not using datetime just date.

              database date and time formats is yyyy-mm-dd (date format) and HH:mm:ss (time format)

              screen date and time formats" is d/m/yy and HH:mm:ss

              It's seems that it does not matter what I put in 'database format' the result is affected by the 'screen format' only...

              I.E. Database = yyyy-mm-dd & Screen = d/m/yy results = 2015-02-08

              Database = d/m/yy & Screen = d/m/yy results = 2015-02-08

              Database = yyyy-mm-dd & Screen = m/d/yy results = 0000-00-00

              Database = yyyy-mm-dd & Screen = mm/dd/yyyy results = 0000-00-00

              and so on, only when I set the Screen formating to yyyy-mm-dd does the correctly formated date get writen to the database...
              I should mention that I've have used this datepicker in a standard DW "insert transaction' not one created by ADDT not sure what difference it makes but just in case.

              Thanks again
              • 4. Re: Date Picker and Formating
                Günter Schenk Level 4
                -----
                I should mention that I've have used this datepicker in a standard DW "insert transaction' not one created by ADDT not sure what difference it makes but just in case.
                -----

                This does make a huge difference, because in this case ADDT´s "screen formats" settings are uninfluential -- you can only benefit from this internal format conversion when using an ADDT insert/update form

                Cheers,
                Günter Schenk
                Adobe Community Expert, Dreamweaver
                • 5. Re: Date Picker and Formating
                  Level 1
                  That makes sense I'll give it a try using ADDT to create the insert page...

                  Thanks for your help.
                  • 6. Re: Date Picker and Formating
                    Level 1
                    Why not change the cron job to use "date +'%s'" (bash shell command) which outputs a unix timestamp (seconds since the epoch, 1970-01-01 at the stroke of midnight)?

                    Then, rather than worrying about lots of text processing one can easily compare numbers.

                    Sometimes it is simpler to change the problem (i.e., clean up a data source) rather than directly solving (i.e., simplify or remove the source of the problem).
                    • 7. Re: Date Picker and Formating
                      Level 1
                      Great idea, I have re-built the page using ADDT already, it only took a few minutes but everything is working perfectly now. Thanks for all your help: I'll be back soon with some questions about multiple image upload.

                      Thanks again.
                      • 8. Re: Date Picker and Formating
                        Level 1
                        You are welcome. I would also suggest having the crontab script simply UPDATE a boolean flag on the rows to mark them as sent rather than deleting them after 14 days. The the select for the emails is:

                        SELECT *
                        FROM `database_schema_name`.`table_name`
                          AS `e`
                        WHERE
                          (`e`.`email_sent`=0) AND
                          (`e`.`when_to_send`<(value_for_midnight_today))

                        Notes:

                        You will need the integer value for midnight today from you scripting environment.

                        The boolean flag email_sent is assumed to be an integer type with the integers zero and one used as values. You can then in the future use this value to record more states.

                        The syntax here is for MySQL due to the quoting used on the integer value.