Link to home
Create AccountLog in
Avatar of codeoxygen
codeoxygen

asked on

how to convert mysql TIMESTAMP value into D-MMM-YYYY, HH:MM AM/PM STF format

i get that timestamp value database through php script and it show like 2013-02-15 17:05:53.

now what i wants is to pass that timestamp value to java script and convert that to D-MMM-YYYY, HH:MM AM/PM STF  format.

here STF = standard time format; depends on location; always shows the standard time for the current location; e.g. If user is in Paris, France, it is “CET”

can anyone clear me with perfect code.
Avatar of Ivo Stoykov
Ivo Stoykov
Flag of Bulgaria image

try something like this
 select date_format(now(), get_format(date,'EUR'));

Open in new window


You could find more about formats here

HTH

Ivo Stoykov
Are you looking like this?

SELECT DATE_FORMAT('2013-02-19 13:26:15', '%d-%b-%Y, %h:%i %p')

--19-Feb-2013, 01:26 PM

Open in new window

Here is the formating types

https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format
Avatar of codeoxygen
codeoxygen

ASKER

thank u Experts for your participation.

Actually while retrieving i got answer like this format  YYYY-MM-DD HH:MM:SS. But i wants to change the format to D-MMM-YYYY, HH:MM AM/PM STF USING JAVA SCRIPT.

in above STF belongs to standard time format; depends on location; always shows the standard time for the current location; e.g. If user is in Paris, France, it is “CET”.

i wants all this conversion using JAVA SCRIPT.

if its possible Experts?
Javascript does not support date formatting. Actually there are some problems using date object in js. Better format in DB (if you get your data from a DB - there is the best place to format output) of server side (like php) and pass the value to your java script code. But this is quite another topic.

HTH

Ivo Stoykov
ASKER CERTIFIED SOLUTION
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Here is the sample code to convert the date format to specified format in javascript as you need

i used the date format function from the http://www.mattkruse.com/javascript/date/date.js file. you can download it and use in your page.

The demo page that uses the javascript file is http://www.mattkruse.com/javascript/date/

check the below sample to convert the MySQL date format to specified format.

<html>
<head>
    <script type="text/javascript" src="http://www.mattkruse.com/javascript/date/date.js"></script>
    <script type="text/javascript" language="javascript">

        function ConvertDateStr() {

            var dateStr = document.getElementById("DateStr").value; // "2013-02-19 13:26:15"
            var dateStrBaseFormat = document.getElementById("DateStrBaseFormat").value; // "yyyy-M-dd HH:mm:ss"
            var dateStrconvertToFormat = document.getElementById("DateStrconvertToFormat").value; // "MMM dd, yyyy hh:mm:ss a"

            var convertedDateStr = formatDate(new Date(getDateFromFormat(dateStr, dateStrBaseFormat)), dateStrconvertToFormat);

            document.getElementById("ConvertedDateStr").value = convertedDateStr;

        }
    </script>
    <style type="text/css">
        #DateStr
        {
            width: 200px;
        }
        #DateStrBaseFormat
        {
            width: 200px;
        }
        #DateStrconvertToFormat
        {
            width: 200px;
        }
        #ConvertedDateStr
        {
            width: 200px;
        }
    </style>
</head>
<body>
    <form id="testForm">
    <table width="600px">
        <tr>
            <td width="60%">Enter Date String
            </td>
            <td width="40%">
                <input type="text" id="DateStr" name="DateStr" value="2013-02-19 13:26:15" size="20" />
            </td>
        </tr>
        <tr>
            <td>Enter Date String's Format (Base Format)
            </td>
            <td>
                <input type="text" id="DateStrBaseFormat" name="DateStrBaseFormat" value="yyyy-M-dd HH:mm:ss"
                    size="20" />
            </td>
        </tr>
        <tr>
            <td>Enter Convertion Date Format
            </td>
            <td>
                <input type="text" id="DateStrconvertToFormat" name="DateStrconvertToFormat" value="MMM dd, yyyy hh:mm:ss a"
                    size="20" />
            </td>
        </tr>
        <tr>
            <td>Converted Date String
            </td>
            <td>
                <input type="text" id="ConvertedDateStr" name="ConvertedDateStr" value="" size="30" />
            </td>
        </tr>
        <tr>
            <td colspan="2" align="center">
                <input type="button" value="Click Me" onclick="javascript:ConvertDateStr();" />
            </td>
        </tr>
    </table>
    </form>
</body>
</html>

Open in new window

Hope this may help you.
thank you experts for your participation,

i like to clarify the question once again.

lets take this scenario, i have clients all over the world. whenever client enter in to my server i wants to show time based on the location of the client.

 which is the best one to show time format in D-MMM-YYYY, HH:MM AM/PM STF using either  PHP or JAVA SCRIPT.