Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2015
    Posts
    27

    Post Unanswered: date format with time stamps

    I have a table where the date and time stamp are logged together and I want to only show the date in a 10 character output. Also I want to return as blank some dates in the field that are 01/01/1800.

    The current table format is 2013-06-28 00:00:00:000
    I just want the date. I was using RTRIM function but it keeps erroring out.

    Thanking you in advance

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You really ought to handle the date formatting at the client end instead of doing the date formatting within SQL Server.

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

  3. #3
    Join Date
    Jan 2015
    Posts
    27
    I am writing it into a script for data mapping
    do you think rtrim should work here..just wanted to show the first 10 characters in the that field

  4. #4
    Join Date
    Jan 2015
    Posts
    27
    this is what I have now


    CASE
    WHEN A.svcdt1 = '18000101'
    THEN
    Convert(nvarchar(20),A.SVCDT1)+'-'+Convert(nvarchar(20),A.SVCDT1)
    ELSE ' '
    END AS Service_Facility,


    and getting the following output

    Jan 1 1800 12:00AM-Jan 1 1800 12:00AM

    the desired output will be only the date in this format

    12/14/2012 (10 character limit)

    and all dates with 1800 year to be blank such as Jan 1 1800 12:00AM-Jan 1 1800 12:00AM ( no time stamp)

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Date[time] data doesn't have "characters" as they are not strings.
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2004
    Posts
    492
    Is SVCDT1 a character field or a datetime?

    If it's datetime, you could try convert(nvarchar(10),A.SVCDT1,101) for the formatting and use year(A.SVCDT1) for the 1800 requirement.

    If it's not a datetime, but for instance a character field, you'll need to either convert it to a date first or use substring to get the right parts.

  7. #7
    Join Date
    Jan 2015
    Posts
    27
    makes sense. It is a date time field.

Tags for this Thread

Posting Permissions

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