Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2008
    Posts
    12

    Unanswered: Converting date to MM/DD/YYYY Format

    Hi,

    I am trying to convert date to MM/DD/YYYY format.

    I am using CONVERT("Date field", SQL_CHAR) but it is returning YYYY-MM-DD format.

    How can i specify style in the CONVERT function ?

  2. #2
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    Short answer, CONVERT doesn't do anything but convert the data type. It does not alter the display in any way.
    You should do that in your application. if you can't do it in your app (like when using Crystal Reports), you could format it your self like:
    Code:
    select convert(month(curdate()),SQL_CHAR) + '/' + convert(day(curdate()),SQL_CHAR) + '/' + convert(year(curdate()),SQL_CHAR)
    Don't forget to convert it back to YYYY-MM-DD before using it in a restriction or an INSERT/UPDATE statement.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  3. #3
    Join Date
    Jan 2008
    Posts
    12
    Thanks for the reply. Another related question is that if i am returning the following format for date of birth in my sql query with date and time. How do i just get the date and not the time.

    The format i am returning is 4/23/2008 12:00:00 .

    Do i use substring, or left function and how would i use it to just get 4/23/2008 ?

    I used SUBSTRING(B."SchDate",1,10) and LEFT(B."SchDate",10) but that did not work.

  4. #4
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    I would actually use the Date functions:
    Code:
    select convert(year(now()),SQL_CHAR)  + '-' + convert(month(now()),SQL_CHAR) + '-' + convert(day(now()),SQL_CHAR)
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

Posting Permissions

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