none
FORMAT Command with TIME Data Type

Answers

  • Hello Kalman,

    FORMAT seems to be a .NET implementation, so you have to use .NET format pattern like "t" for short time; see
    Standard Date and Time Format Strings
    Custom Date and Time Format Strings

    But it also seems, that the customer date format don't work proper, example:

    DECLARE @t char(4) = '1605';
    DECLARE @tm time = STUFF(@t,3,1,':');
    
    SELECT @t, 
           @tm, 
           FORMAT(@tm, N't', N'en-US' ) AS ShortTime,
           FORMAT(@tm, N'T', N'en-US' ) AS LongTime,
           FORMAT(@tm, N'hh', N'en-US' ) AS HoursOnly,
           FORMAT(@tm, N'hhmm', N'en-US' ) AS HoursMinute,
           FORMAT(@tm, N'hhmmss', N'en-US' ) AS HoursMinuteSeconds,
           FORMAT(@tm, N'hh:mm:ss', N'en-US' ) AS ThisDoNotWork


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, September 25, 2013 6:43 AM

All replies

  • Check the below code. I hope this is what you are after.

    DECLARE @t CHAR(4) = '1600';
    DECLARE @tm TIME = Stuff(@t, 3, 1, ':');

    SELECT @t,
           @tm

    SELECT @t,
           CONVERT(TIME(0), @tm) 

    Wednesday, September 25, 2013 12:42 AM
  • I am after the FORMAT command with TIME data type.

    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Wednesday, September 25, 2013 4:18 AM
    Moderator
  • Hi Kalman,

    Below link may below you.

    http://technet.microsoft.com/en-us/library/hh213505.aspx

    Regards,

    Kccrga

    Wednesday, September 25, 2013 4:32 AM
  • Hi kalman,

    Its not possible to format into time with Format command with input passed as string or integer. 

    It basically format the input passed to various local format. For datatype conversion you need to use CAST /CONVERT Function.

    If really want to get time for any other local format, the first argument of the format has to be TIME or DateTime datatype.

    For Example: 

    DECLARE @t datetime  = getdate();
    SELECT @t,  FORMAT(@t, 'hh:mm:ss', 'en-US')
    
    /*
    This will work
    */
    go
    
    DECLARE @t varchar(50)  = '2013-09-25 10:53:58.210';
    SELECT @t,  FORMAT(@t, 'hh:mm:ss', 'en-US')
    /*
    Error:
    Msg 8116, Level 16, State 1, Line 3
    Argument data type varchar is invalid for argument 1 of format function.
    */
    go
    
    DECLARE @t varchar(50)  = '2013-09-25 10:53:58.210';
    SELECT @t,  FORMAT(Cast(@t as datetime), 'hh:mm:ss', 'en-US')
    
    /*
    This will work
    */
    go


    Regards Harsh

    Wednesday, September 25, 2013 5:26 AM
  • >the first argument of the format has to be TIME or DateTime datatype

    Look at my example, I am passing TIME.


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Wednesday, September 25, 2013 6:21 AM
    Moderator
  • Hello Kalman,

    FORMAT seems to be a .NET implementation, so you have to use .NET format pattern like "t" for short time; see
    Standard Date and Time Format Strings
    Custom Date and Time Format Strings

    But it also seems, that the customer date format don't work proper, example:

    DECLARE @t char(4) = '1605';
    DECLARE @tm time = STUFF(@t,3,1,':');
    
    SELECT @t, 
           @tm, 
           FORMAT(@tm, N't', N'en-US' ) AS ShortTime,
           FORMAT(@tm, N'T', N'en-US' ) AS LongTime,
           FORMAT(@tm, N'hh', N'en-US' ) AS HoursOnly,
           FORMAT(@tm, N'hhmm', N'en-US' ) AS HoursMinute,
           FORMAT(@tm, N'hhmmss', N'en-US' ) AS HoursMinuteSeconds,
           FORMAT(@tm, N'hh:mm:ss', N'en-US' ) AS ThisDoNotWork


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, September 25, 2013 6:43 AM
  • It seems FORMAT is having trouble accepting timeas input.
    DECLARE @t char(4) = '1600';
    DECLARE @vtm time = STUFF(@t,3,1,':'), @vdttm datetime = STUFF(@t,3,1,':'), @vdt date = STUFF(@t,3,1,':')
    
    SELECT  FORMAT(@vtm , 'HH:mm:ss', 'en-US'),
    FORMAT(@vdt, 'HH:mm:ss', 'en-US'), ---Works fine
    FORMAT(@vdttm, 'HH:mm:ss', 'en-US')--Works fine


    Satheesh
    My Blog



    Wednesday, September 25, 2013 6:51 AM
  • Sorry, my mistake it should be DateTime only.

    Clearly mention in msdn article - > http://technet.microsoft.com/en-us/library/hh213505.aspx, Please check the remark section.

    for your code, you can try something like this:

    DECLARE @t char(4) = '1600';
    DECLARE @tm datetime = '1900-01-01'  + ' ' + STUFF(@t,3,1,':');
    SELECT @t, @tm, FORMAT(@tm, 'hh:mm:ss', 'en-US')



    Regards Harsh

    Wednesday, September 25, 2013 6:56 AM
  • 1. Olaf,

    your query need a minor fix :-)

    don't use 

    DECLARE @tm time = STUFF(@t,3,1,':');

    but

    DECLARE @tm time = STUFF(@t,3,0,':');

    As you dont want to replace one char

    check the time '1615' in your query and you will get like '1605' as the '1' was replace with ':'

    2. there is more simple way in this case. we can just use datetime and not time from the start

    DECLARE @tm datetime

    In this case the last column (and the original query) will work too (using Olaf example, some of the results will be in12 hour formatted time)

    DECLARE @tm datetime



    [Personal Site] [Blog] [Facebook]signature

    • Edited by pituach Wednesday, September 25, 2013 8:42 AM
    Wednesday, September 25, 2013 8:33 AM