CODEWITHSUNDEEP
sqlsql-server
user3518032
user3518032

Reputation: 423

Sql returning date time without seconds and milliseconds

my this code returns date time but with seconds milliseconds , i don't want milliseconds and seconds. I tried but not working, help. In this format. Output:

Code:

Set @DateFrom = Convert(date,(Select min(ReceivedMessages.ReceivedDateTime) from ReceivedMessages))
      Set @DateTo = Convert(date,(Select max(ReceivedMessages.ReceivedDateTime) from ReceivedMessages))

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

Upvotes: 0

Views: 9193

Answers (3)

How &#39;bout a Fresca
How &#39;bout a Fresca

Reputation: 2317

Try this, but replace GETDATE() with your time

SELECT CONVERT(VARCHAR(16), GETDATE(), 121)

Upvotes: 3

Dave.Gugg
Dave.Gugg

Reputation: 6771

You can round datetime to the nearest minute like this:

DECLARE @CaptureDate datetime
SELECT @CaptureDate = DATEADD(minute, DATEDIFF(minute, 0, DATEADD(second, 30 - DATEPART(second, GETDATE() + '00:00:30.000'), GETDATE())), 0)
SELECT  CONVERT(VARCHAR(16), @CaptureDate, 120)

Change GETDATE() to your date fields as applicable.

Upvotes: 0

Tak
Tak

Reputation: 1562

declare @dtime datetime;
set @dtime = getdate();

select REPLACE(CONVERT(varchar(11), @dtime, 113), ' ', '/') 
     + RIGHT(CONVERT(varchar(17), @dtime, 113), 6)

Upvotes: 1

Related Questions