Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    8

    Unanswered: Convert Date with T-SQL

    Hi,

    problem with transact-SQL (SQL2k). I'm gathering informations from2 different tables and the column that interest me (the invoice date) have same format but not filled in the same. One is an old table and the format is "datetime" (but written like dd/mm/yyyy) and the new one "datetime" as well but "dd/mm/yyyy hh:mm:ss" (and it's not small datetime)
    I'd like to convert the date format wich appears with the "hh:mm:ss" to a smaller format (just dd/mm/yyyy).
    I'v tried with the conversion into text (the CONVERT function limits) wich works but I need to deal with a date format when I get the result. If I re convert to smalldatetime, I get the hh:mm:ss again.

    Hope this clear.

    Thanks

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    DDL and some sample data would be a BIG help....
    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.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    SQL Server always stores the date and time. There isn't any way to change that.

    You can change how your client displays what SQL Server returns. I think that is what you need to address.

    -PatP

  4. #4
    Join Date
    Apr 2004
    Posts
    6

    New Invoice Date

    Select ShortInvoiceDt = CONVERT(VARCHAR(10), NewerInvoiceDate, 101)
    FROM NewerTable

    Use the client app to format accordingly. For example, are you using Access? Crystal? VB?

  5. #5
    Join Date
    Mar 2004
    Posts
    8
    I'm testing differents client apps, like Business Object and Harry soft as the data are re stored in a datawarehouse DB.

    And yes, seems I don't have the choice, deal with the client App.

    Brett, Do you need more details like example?

    Thanks for your posts, I'll spare time now searching on the wright place.

    Cheers

  6. #6
    Join Date
    Apr 2004
    Location
    The Netherlands
    Posts
    29

    Talking Convert Date with T-SQL

    In addition to the other reply's,
    maybe this will also help some people who read this topic...
    ---------------------------------------------------------
    declare
    @invoice_date datetime,
    @new_date varchar(10)

    set @invoice_date = getdate()
    -- 'Cut' the time part
    select @new_date = convert(varchar,@invoice_date,103)
    -- Display date
    select @new_date as new_date
    -- Work with the date in date format with the time set to zero and convert it later to character for displaying
    -- only the date without time part
    select convert(datetime,@new_date,103) as working_date

Posting Permissions

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