Convert function in sql

Question

I am using this query to display date and time. I have used the 113 in conversion function that gave me the date format with spaces and i replaced it with a /, now i just want to take out the / that separates date from time.

 SELECT [ID]
      ,REPLACE(convert(varchar, ReceivedMessages.ReceivedDateTime, 113), ' ','/') as RecievingDate
      FROM [CmsSMSDb].[dbo].[ReceivedMessages] 
      where Convert(date,ReceivedDateTime)>= @DateFrom AND Convert(date,ReceivedDateTime)<= @DateTo

It gives this result:

09/Jun/2014/10:04:07:000

That's what i want, it works perfectly but problem is that i want to remove '/' after 2014, like 09/Jun/2014 10:04:07:000

but in other places '/' this should remain same but it should not appear after 2014.

how ?

Answer 1

If you are using SQLServer 2012 or better you can use the new function FORMAT

SELECT [ID]
     , FORMAT(ReceivedDateTime, 'dd/MMM/yyyy hh:mm:ss:ms') as RecievingDate
FROM   [CmsSMSDb].[dbo].[ReceivedMessages] 
WHERE  Convert(date,ReceivedDateTime)>= @DateFrom 
  AND  Convert(date,ReceivedDateTime)<= @DateTo

In the FORMAT function hh is for 12-hour format, HH for 24-hour format, you can add tt to get AM or PM, to see the difference try

SELECT FORMAT(SYSDATETIME()(), 'dd/MMM/yyyy hh:mm:ss:ms tt') _12
     , FORMAT(SYSDATETIME()(), 'dd/MMM/yyyy HH:mm:ss:ms') _24

Also you should not change the data stored (that can be in a index) to match a parameter, if should be the other way around

SELECT [ID]
     , FORMAT(ReceivedDateTime, 'dd/MMM/yyyy hh:mm:ss:ms') as RecievingDate
FROM   [CmsSMSDb].[dbo].[ReceivedMessages] 
WHERE  ReceivedDateTime >= Cast(@DateFrom AS DateTime2)
  AND  ReceivedDateTime <= DateAdd(dd, 1, DateAdd(ms, -1, Cast(@DateTo as DateTime2)))

Answer 2

Just remove the replace function

SELECT [ID], 
       convert(varchar, ReceivedMessages.ReceivedDateTime, 113) as RecievingDate
  FROM [CmsSMSDb].[dbo].[ReceivedMessages] 
 where Convert(date,ReceivedDateTime) >= @DateFrom 
   AND Convert(date,ReceivedDateTime)<= @DateTo

Answer 3

Dirty but works:

SELECT 
      LEFT(REPLACE(convert(varchar, ReceivedMessages.ReceivedDateTime, 113), ' ','/'), 11) + ' ' +
      RIGHT(REPLACE(convert(varchar, ReceivedMessages.ReceivedDateTime, 113), ' ','/'), 12)
      as RecievingDate
FROM [CmsSMSDb].[dbo].[ReceivedMessages] 
      where Convert(date,ReceivedDateTime)>= @DateFrom AND Convert(date,ReceivedDateTime)<= @DateTo

Answer 4

Use below query :

SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ' ', '/') + ' '
       +  CONVERT(VARCHAR(8), GETDATE(), 108) as [DD/Mon/YYYY HH:MM:SS]

In your query as:

SELECT [ID]
,REPLACE(CONVERT(VARCHAR(11),ReceivedMessages.ReceivedDateTime , 106), ' ', '/') + ' '
+  CONVERT(VARCHAR(8), ReceivedMessages.ReceivedDateTime , 108) as RecievingDate
FROM [CmsSMSDb].[dbo].[ReceivedMessages] 
where Convert(date,ReceivedDateTime)>= @DateFrom 
AND Convert(date,ReceivedDateTime)<= @DateTo

[Edit] For 12 hr format write as :

SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ' ', '/') + ' '
+ substring(CONVERT(VARCHAR, GETDATE(), 109),13,
Len (CONVERT(VARCHAR, GETDATE(), 109))) as [DD/Mon/YYYY HH:MI:SS:MMMAM (or PM)]

and modify your query as:

SELECT [ID]
,REPLACE(CONVERT(VARCHAR(11),ReceivedMessages.ReceivedDateTime , 106), ' ', '/') 
+ ' '
+  substring(CONVERT(VARCHAR, ReceivedMessages.ReceivedDateTime , 109),13,
Len (CONVERT(VARCHAR, ReceivedMessages.ReceivedDateTime , 109))) as RecievingDate
FROM [CmsSMSDb].[dbo].[ReceivedMessages] 
where Convert(date,ReceivedDateTime)>= @DateFrom 
AND Convert(date,ReceivedDateTime)<= @DateTo

Answer 5

select [ID]
     , replace(left(convert(varchar,getdate(),113),11),' ','/')+
       right(convert(varchar,getdate(),113),13) 
       as RecievingDate
FROM [CmsSMSDb].[dbo].[ReceivedMessages] 
       where Convert(date,ReceivedDateTime)>= @DateFrom 
       AND Convert(date,ReceivedDateTime)<= @DateTo

you only need to use the replace function on the 1st part, not on the 2nd part like in robertw's answer.