Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2014
    Posts
    35

    Unanswered: Display only the date part of a date & time field?

    Hi Everyone,

    I want to display only the date part of a date field which contains both date & time information.

    For example I have the value '2013-11-14 00:00:00.000' in my result set, and ideally I would like to show only '2013-11-14'.

    I have looked up the datepart() command, however I can't work out how to return all parts of the date, rather than just the year, month, or day.

    Any help here will be greatly appreciated.

    Kind Regards,

    David

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Formatting is normally done on the client, not at the database server. This allows for different locale specific formatting to be applied by each client as necessary. Dates are formatted quite differently in different parts of the world, as well as for different applications like a Data Warehouse.

    If you truly want to convert the dates into a character form (like what TO_CHAR() does in PL/SQL), you can use the SQL Server Convert() function to change the data type and control the format of the string.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If this is simply for display purposes then leave that up to your front-end application.

    If this is for functional grouping, or similar, in your T-SQL then:

    2005 and earlier:
    Code:
    SELECT your_date
         , DateAdd(dd, DateDiff(dd, 0, your_date), 0) As date_only
    FROM   your_table
    2008 onwards:
    Code:
    SELECT your_date
         , Cast(your_date As date) As date_only
    FROM   your_table
    George
    Home | Blog

  4. #4
    Join Date
    Mar 2014
    Posts
    35
    Thanks for the helpful responses.

Posting Permissions

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