Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Posts
    6

    Question Unanswered: PHP and MSSQL Date Formats

    I have created a view in SQL Server 2000 that returns dates in the format dd/mm/yyyy.

    When I retrieve the data using a web page via PHP, the same date is returned as dd MMM yyyy tt e.g. '27 Oct 2003 0:00'.

    I'm sure I've seen somewhere that PHP and/or MSSQL uses some sort of default format when returning data to HTML.

    How do I change the format to return data in the same format as the view?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    why not just define the view to return the date in the desired format using sql server's CONVERT function

    that way the date will come intp php as a character string and cannot possibly be mangled

    create view querywithmydateformat
    as
    select foo, bar, convert(char(10),thedatefield,103) as thedatefield
    ...

    see CAST and CONVERT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2003
    Posts
    6
    Thanks for the reply.

    However, your suggestion will not provide the correct sort order on dates.

    Date values will be sorted by their character value, not their date value. For example 29/09/2003 will appear AFTER 10/10/2003.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    create view querywithmydateformat
    as
    select foo, bar, thedatefield
    , convert(char(10),thedatefield,103) as formatteddatefield
    from yourtable


    then

    select foo, bar, formatteddatefield
    from querywithmydateformat
    order by thedatefield



    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    34

    Re: PHP and MSSQL Date Formats

    You can use the DATE_FORMAT() function if you choose to do the conversion in MySQL. For example:

    Code:
    SELECT DATE_FORMAT(date_column, '%m %d %Y')
    FROM tablename
    where:

    %m = Month, numeric, leading zeroes
    %d = Day of month, numeric, leading zeroes
    %Y = Year, numeric, 4-digits




    Originally posted by jxh
    I have created a view in SQL Server 2000 that returns dates in the format dd/mm/yyyy.

    When I retrieve the data using a web page via PHP, the same date is returned as dd MMM yyyy tt e.g. '27 Oct 2003 0:00'.

    I'm sure I've seen somewhere that PHP and/or MSSQL uses some sort of default format when returning data to HTML.

    How do I change the format to return data in the same format as the view?
    DigiOz Multimedia
    http://www.digioz.com

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    MSSQL = microsoft sql server, not mysql

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    34
    Oops. Looks like I read the database type wrong. The above code is for MySQL, not MS SQL. Sorry about that.
    DigiOz Multimedia
    http://www.digioz.com

Posting Permissions

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