none
Is there a simpler way to format a datetime? RRS feed

  • Question

  • declare @date1 datetime
    set @date1 = CAST('2011-01-01 18:11' as datetime)
    declare @date2 char(5)
    set @date2 = CAST(DATEPART(hh,@date1) as char(2))  + ':' + CAST(DATEPART(n,@date1) as CHAR(2))
    select @date2

    -- Is this the best way to set @date2 to 18:11

    -- There is a problem of time 18:01 being displayed as 18:1 also

    -- Is there a way to tell it I want a leading zero?


    -- It seems a bit complex compared to formatting in code

    -- This really is a mess, so I guess code is the way I'll be doing it for now unless someone has a better idea.

     

     



    • Edited by cupboy2 Friday, January 27, 2012 7:50 PM
    Friday, January 27, 2012 7:46 PM

Answers

  • Have you tried this?

    declare @date1 datetime
    set @date1 = CAST('2011-01-01 18:11' as datetime)
    declare @date2 char(5)
    set @date2 = CONVERT(TIME,@date1)
    select @date2 
    



    Abdallah El-Chal, PMP, CIPP/IT, ITIL
    • Marked as answer by cupboy2 Friday, January 27, 2012 8:35 PM
    Friday, January 27, 2012 7:48 PM

All replies

  • Have you tried this?

    declare @date1 datetime
    set @date1 = CAST('2011-01-01 18:11' as datetime)
    declare @date2 char(5)
    set @date2 = CONVERT(TIME,@date1)
    select @date2 
    



    Abdallah El-Chal, PMP, CIPP/IT, ITIL
    • Marked as answer by cupboy2 Friday, January 27, 2012 8:35 PM
    Friday, January 27, 2012 7:48 PM
  • If you want time only, check CONVERT function in BOL and style 108.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Friday, January 27, 2012 7:52 PM
    Moderator
  • You want to store only the time?

    select substring(convert(varchar(20),@date1,120),12,5) as date2

    Friday, January 27, 2012 7:53 PM
  • Why are you formatting data in the back end?  The basic principle of a tiered architecture is that display is done in the front end and never in the back end.  This is a more basic programming principle than just SQL and RDBMS.  
    We now have DATE and TIM data types; just use CAST(@in_something_date AS TIME(0)), which will give you the ISO-8601 format "hh:mm:ss" and then your application can clip the second if you want to precision.
    Oh, never5 use the old Sybase CONVERT() museum function. Back in the early days people used to format data in the T-SQL dialect and things like MONEY, CONVERT and a few others are left overs from that decade. Client-Server had not been invented  yet, so there was not a firm distinction in the tiers. We were still thinking in COBOL. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
    Saturday, January 28, 2012 3:16 AM