0

In SQL server, date is getting saved in the following format:-

2017-02-20 05:59:58.537

But I need the output in the following format:

20/Feb/2017 11:29:58 AM
Asad
  • 11
  • 1
  • Refer: http://www.sql-server-helper.com/tips/date-formats.aspx – Kinchit Dalwani Feb 20 '17 at 06:40
  • Why are you storing dates as `VARCHAR`? –  Feb 20 '17 at 07:04
  • SQL stores the date as per the default setting. while fetching that date you need to convert that date according to your requirement. You can try this http://www.sql-server-helper.com/sql-server-2008/sql-server-2008-date-format.aspx – Ajay Prajapati Feb 20 '17 at 08:35

6 Answers6

2

Try This 

select  replace(convert(varchar(11),GETDATE(),113), ' ', '/')+ ' '+    RIGHT(CONVERT(varchar(20), GETDATE(), 22), 11);
mangesh
  • 355
  • 4
  • 13
  • 1
    @Asad: If an answer solved your problem you should accept it so that other people will know that the problem is solved. – Zohar Peled Feb 20 '17 at 06:54
  • @zohar query is not working fine after 12 hours after 12 it should come as 01:10:15 PM but now its showing as 13:10:15 PM .. can we add AM and PM in 24 hours format ? – Asad Feb 20 '17 at 10:09
0

Try this,

SELECT replace(convert(NVARCHAR, getdate(), 106), ' ', '/')+' '+right(getdate(),7)
Thangadurai.B
  • 561
  • 1
  • 3
  • 18
  • Thanks for your response , but its not getting the output as per its given in question. Missing is 'seconds' – Asad Feb 20 '17 at 06:43
0
Try below conversion method :

 DECLARE @DATE DATETIME = '2017-02-20 05:59:58.537'
 SELECT REPLACE(CONVERT(VARCHAR(11),@DATE,113), ' ', '/') + ' ' 
 +CONVERT(VARCHAR(20),@DATE,108)+' ' +RIGHT(@DATE,2)
Mansoor
  • 4,061
  • 1
  • 17
  • 27
0

SQL stores the date as per the default setting (yyyy-mm-dd hh:mm:ss:ms). while fetching that date you need to convert that date according to your requirement.

Try this

Ajay Prajapati
  • 63
  • 1
  • 10
  • Wrong. [Sql server stores dates as numbers.](http://stackoverflow.com/questions/30032915/how-to-cast-the-datetime-to-time/30033028#30033028) – Zohar Peled Feb 20 '17 at 10:12
0

Correct Answer:

SELECT REPLACE(CONVERT(VARCHAR(11), getdate(), 113), ' ', '/') + ' ' + CONVERT(VARCHAR(20), CONVERT(TIME, getdate()), 22)
sazzad
  • 5,740
  • 6
  • 25
  • 42
Asad
  • 11
  • 1
0

I have a date Function I use, and you could modify it to include details for hours, minutes and seconds, too. 

CREATE FUNCTION [dbo].[fn_DateDisplay]
(
    -- Add the parameters for the function here
    @dateIn date
)
RETURNS nvarchar(MAX)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @dateOut nvarchar(MAX)

    DECLARE @yearIn int
    DECLARE @monthIn int
    DECLARE @dayIn int

    DECLARE @monthOut nvarchar(3)

    -- Add the T-SQL statements to compute the return value here
    SET @yearIn = YEAR ( @dateIn ) 
    SET @monthIn = MONTH ( @dateIn ) 
    SET @dayIn = DAY ( @dateIn ) 

    SET @monthOut = SUBSTRING ( DATENAME ( MONTH , @dateIn ) , 1 , 3 )

    SET @dateOut = CONCAT ( @dayIn , ' ' , @monthOut , ' ' , @yearIn )

    -- Return the result of the function
    RETURN @dateOut

END
Lewis Stairs
  • 3
  • 3