Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2003
    Location
    California
    Posts
    40

    Unanswered: formatting dates

    Hi, I was wondering if there is a way to output a special format for dates in SELECT statements. Currently, my date field is returning my date in this format:
    2004-01-19 00:00:00.000
    but I want it to be like this:
    1/19/04 or 1/19/2004
    Is there a way to do this?
    Thanks.

  2. #2
    Join Date
    Oct 2003
    Location
    Buenos Aires, Argentina
    Posts
    20
    Try this:

    SELECT CONVERT(char(10),getdate(),103)
    ------------------------------------
    Thanx
    ------------------------------------
    http://www.iespana.es/greatavatar/FIRMA/firma.jpg

  3. #3
    Join Date
    Oct 2003
    Location
    Buenos Aires, Argentina
    Posts
    20
    Please.. use the 101 code for the date format..

    like this:

    SELECT CONVERT(char(10),getdate(),101)

    the other example is another format.

    You can consult all the date format in the SQL SERVER BOOKS ON LINE!.
    ------------------------------------
    Thanx
    ------------------------------------
    http://www.iespana.es/greatavatar/FIRMA/firma.jpg

  4. #4
    Join Date
    Dec 2003
    Location
    California
    Posts
    40
    But doesn't that just return the current date?
    Here's my SQL statement:
    Code:
    SELECT id, FirstName, LastName, TerrNumber, IsCheckedOut, IsLPCheckedOut, FirstNameLP, LastNameLP, checkedOutDate, ReturnedDate FROM Checkouts";
    checkedOutDate and ReturnedDate are the fields I want to format. How would this work with getdate()?

  5. #5
    Join Date
    Jan 2003
    Location
    Pittsburgh, PA
    Posts
    86
    Just replace the getdate() with checkedOutDate. Do the same again with ReturnedDate (a separate query).


    Originally posted by domiflichi
    But doesn't that just return the current date?
    Here's my SQL statement:
    Code:
    SELECT id, FirstName, LastName, TerrNumber, IsCheckedOut, IsLPCheckedOut, FirstNameLP, LastNameLP, checkedOutDate, ReturnedDate FROM Checkouts";
    checkedOutDate and ReturnedDate are the fields I want to format. How would this work with getdate()?

  6. #6
    Join Date
    Aug 2003
    Posts
    328
    Look at my post from yesterday. I had the same question.
    exdter

  7. #7
    Join Date
    Dec 2003
    Location
    California
    Posts
    40
    That's what I thought you're supposed to do. But when I do that...here's my new SQL statement:
    Code:
    SELECT id, FirstName, LastName, TerrNumber, IsCheckedOut, IsLPCheckedOut, FirstNameLP, LastNameLP, CONVERT(char(10),checkedOutDate,101), ReturnedDate FROM Checkouts
    I get an error when trying to display my results of the query when encountering that field:

    Warning: odbc_result(): Field checkedoutdate not found in d:\web\territories\index.php on line 173

    If it helps: my webserver is Win2k Pro running Apache and PHP, and my SQL server is on Win2K server and it's actually MSDE 2000.

  8. #8
    Join Date
    Dec 2003
    Location
    California
    Posts
    40
    Originally posted by exdter
    Look at my post from yesterday. I had the same question.
    exdter

    I'm getting the same error message on my query:

    Warning: odbc_result(): Field checkedoutdate not found in d:\web\territories\index.php on line 174


    Here's the SQL statement:
    Code:
    SELECT id, FirstName, LastName, TerrNumber, IsCheckedOut, IsLPCheckedOut, FirstNameLP, LastNameLP, CONVERT(char(10),CONVERT(datetime, CAST(checkedOutDate as varchar(12))),103), ReturnedDate FROM Checkouts
    Why isn't this working for me?
    Thanks,
    domiflichi

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Try this

    Code:
    SELECT id, FirstName, LastName, TerrNumber, IsCheckedOut, IsLPCheckedOut, FirstNameLP, LastNameLP
    , CONVERT(char(10),CONVERT(datetime, CAST(checkedOutDate as varchar(12))),103) AS checkedOutDate, ReturnedDate FROM Checkouts
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Dec 2003
    Location
    California
    Posts
    40
    Thank you Brett!...it worked! And thank you to everybody else for getting me through each step, and being patient with this newbie. And thank you all for such quick replies!

Posting Permissions

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