none
How to convert Date to mm/dd/yyyy hh:mm:ss: AM PM

    Question

  • Hi  all,

    I need to convert date time to format  mm/dd/yyyy hh:mm:ss: AM PM in stored procedure. For example: 01/07/2011 04:10:41 PM

    Currently i only manage to format the date up to date level only. My code is as per below

    select CONVERT(VARCHAR(10),RequestDelDate, 101)

    But i am not able to covert the time to exactly what i want.I need leading zero in hour, minutues and second if there is only 1 digit.

    The closet way i get is 
    SELECT convert(varchar, getdate(), 22)

    Does anyone has any idea to have leading zero in fornt of time? Help Please!!

    Thursday, April 19, 2012 7:48 AM

Answers

  • pls try

    select convert(varchar(20),GETDATE(),101)+ ' '+convert(varchar(20),GETDATE(),108)+ ' ' +right(convert(varchar(30),GETDATE(),109),2)

    op

    04/19/2012 09:31:13 AM

    VT


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker




    Thursday, April 19, 2012 8:29 AM
  • That is a beast:

    DECLARE @dt DATETIME = Getdate(); 
    DECLARE @time VARCHAR(32) = CONVERT(VARCHAR, @dt, 109); 
    
    SELECT CONVERT(CHAR(10), @dt, 101) + ' '+RIGHT('0'+
    									 Substring(@time, Len(@time) - 
                                         Charindex(' ', Reverse(@time)) +2, 
                                         Charindex(' ', Reverse(@time)) - 
                                         Charindex(':', Reverse(@time))-1),8) 
           + ' '+RIGHT(@time, 2);  
    -- 04/19/2012 09:27:52 AM       

    String datetime conversion article:

    http://www.sqlusa.com/bestpractices/datetimeconversion/


    Kalman Toth SQL SERVER & BI TRAINING


    Thursday, April 19, 2012 8:36 AM
    Moderator
  • Hello John,
     
    Please check the following page for SQL date time convert options
     
    What I see there is using the option 22 for your requirement as you said
    declare @t datetime = '11/30/2012 04:10:41 PM'
    select convert(varchar(50),@t,22)
    Returns "11/30/12  4:10:41 PM"
     
    You only need to change year part from 12 to 2012

    select 
    substring(replace(convert(varchar(50),@t,22),'  ', ' 0'), 1,6)+'20'+
    substring(replace(convert(varchar(50),@t,22),'  ', ' 0'), 7,14)
    


    SQL Server, SQL Server 2012 Denali and T-SQL Tutorials

    • Marked as answer by johnsonlim026 Friday, April 20, 2012 1:14 AM
    Thursday, April 19, 2012 8:39 AM
    Moderator
  • If I understand corrct, the OP want leadung zeros in hour, minute and second.

    So I assume, that there ist no buildin style to achieve that.

    I created a function:

    create function udf_DateToString (@datein datetime) 
    returns varchar(50)
    as
    begin
    declare @minute char(2), 
    		@hour char(2), 
    		@second varchar(5), 
    		@time varchar(20), 
    		@date char(8), 
    		@dateout varchar(50)
    select  @date = left(convert(varchar(20),@datein,22),8), @time = ltrim(replace(substring(convert(varchar(20),@datein,22),10,20),':','.'))
    select @second = right('00' + parsename(@time,1),5), @minute = right('00' + parsename(@time,2),2), @hour = right('00' + parsename(@time,3),2)
    select @dateout = @date + ' ' + @hour + ':' + @minute + ':' + @second
    return @dateout
    end
    select dbo.udf_DateToString (cast('20120419 09:05:04' as datetime))

    • Marked as answer by johnsonlim026 Friday, April 20, 2012 1:17 AM
    Thursday, April 19, 2012 9:10 AM

All replies

  • pls try

    select convert(varchar(20),GETDATE(),101)+ ' '+convert(varchar(20),GETDATE(),108)+ ' ' +right(convert(varchar(30),GETDATE(),109),2)

    op

    04/19/2012 09:31:13 AM

    VT


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker




    Thursday, April 19, 2012 8:29 AM
  • That is a beast:

    DECLARE @dt DATETIME = Getdate(); 
    DECLARE @time VARCHAR(32) = CONVERT(VARCHAR, @dt, 109); 
    
    SELECT CONVERT(CHAR(10), @dt, 101) + ' '+RIGHT('0'+
    									 Substring(@time, Len(@time) - 
                                         Charindex(' ', Reverse(@time)) +2, 
                                         Charindex(' ', Reverse(@time)) - 
                                         Charindex(':', Reverse(@time))-1),8) 
           + ' '+RIGHT(@time, 2);  
    -- 04/19/2012 09:27:52 AM       

    String datetime conversion article:

    http://www.sqlusa.com/bestpractices/datetimeconversion/


    Kalman Toth SQL SERVER & BI TRAINING


    Thursday, April 19, 2012 8:36 AM
    Moderator
  • Hello John,
     
    Please check the following page for SQL date time convert options
     
    What I see there is using the option 22 for your requirement as you said
    declare @t datetime = '11/30/2012 04:10:41 PM'
    select convert(varchar(50),@t,22)
    Returns "11/30/12  4:10:41 PM"
     
    You only need to change year part from 12 to 2012

    select 
    substring(replace(convert(varchar(50),@t,22),'  ', ' 0'), 1,6)+'20'+
    substring(replace(convert(varchar(50),@t,22),'  ', ' 0'), 7,14)
    


    SQL Server, SQL Server 2012 Denali and T-SQL Tutorials

    • Marked as answer by johnsonlim026 Friday, April 20, 2012 1:14 AM
    Thursday, April 19, 2012 8:39 AM
    Moderator
  • pls try

    select convert(varchar(20),GETDATE(),101)+ ' '+convert(varchar(20),GETDATE(),108)+ ' ' +right(convert(varchar(30),GETDATE(),109),2)

    op

    04/19/2012 09:31:13 AM

    VT


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker




    That will not work for time after noon:

     declare @d datetime
     set @d = '20120419 13:05:04'
     select convert(varchar(20),@d,101)+ ' '+convert(varchar(20),@d,108)+ ' ' +right(convert(varchar(30),@d,109),2)

    result: 04/19/2012 13:05:04 PM

    Should be 04/19/2012 01:05:04 PM

    Thursday, April 19, 2012 9:07 AM
  • If I understand corrct, the OP want leadung zeros in hour, minute and second.

    So I assume, that there ist no buildin style to achieve that.

    I created a function:

    create function udf_DateToString (@datein datetime) 
    returns varchar(50)
    as
    begin
    declare @minute char(2), 
    		@hour char(2), 
    		@second varchar(5), 
    		@time varchar(20), 
    		@date char(8), 
    		@dateout varchar(50)
    select  @date = left(convert(varchar(20),@datein,22),8), @time = ltrim(replace(substring(convert(varchar(20),@datein,22),10,20),':','.'))
    select @second = right('00' + parsename(@time,1),5), @minute = right('00' + parsename(@time,2),2), @hour = right('00' + parsename(@time,3),2)
    select @dateout = @date + ' ' + @hour + ':' + @minute + ':' + @second
    return @dateout
    end
    select dbo.udf_DateToString (cast('20120419 09:05:04' as datetime))

    • Marked as answer by johnsonlim026 Friday, April 20, 2012 1:17 AM
    Thursday, April 19, 2012 9:10 AM
  • look at this

     Declare @vch varchar(35) = '01/07/2011 04:10:41 PM';
     select @vch, cast(@vch as datetime)

    and result is..

    (No column name)                    (No column name)
    01/07/2011 04:10:41 PM             2011-01-07 16:10:41.000


    Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com

    Thursday, April 19, 2012 9:14 AM
  • One more

    declare @t varchar(50)

    select  @t=right(convert(varchar, dateadd(mi, 3.5 * 60, 'jan 1 1900'), 100), 7)
    select @t
    -----------PM,AM
    SELECT REPLACE(REPLACE(LTRIM(RIGHT(CONVERT(VARCHAR(32), 
    CONVERT(SMALLDATETIME, '17:30'), 0),7)), 'AM', ' AM'), 'PM', ' PM');


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Thursday, April 19, 2012 9:22 AM
    Answerer
  • Hi Shamas... Stop proposing your own responses as answers... You'll accumulate your points anyway.
    Thursday, April 19, 2012 1:15 PM
  • :) don't panic.

    Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com

    Friday, April 20, 2012 5:43 AM