CODEWITHSUNDEEP
sqldatabasesql-server-2008-r2
C Sharper
C Sharper

Reputation: 8646

Remove seconds from datetime

I have query as below:

select  
 lower(convert(varchar(10), GETDATE(), 101) +
 ' '+ 
 substring(convert(varchar(20), GETDATE(), 22),9,Len(GETDATE())))

In this query i am getting result as:

06/27/2014   6:23:57 pm

I just want to remove seconds (i.e. 57) from above.

My intentded result is:

06/27/2014   6:23 pm

How can i obtain it??

I tried adding Length in the query as:

select  
 lower(convert(varchar(10), GETDATE(), 101) +
 ' '+ 
 substring(convert(varchar(20), GETDATE(), 22),9,Len(GETDATE())+1))

But it didnt help.

Plase help me.

Upvotes: 0

Views: 2769

Answers (3)

David Brabant
David Brabant

Reputation: 43539

declare @date datetime

select @date = GetDate()

select convert(nvarchar, @date, 101) + ' ' + right(convert(nvarchar, @date, 100), 6)

Upvotes: 1

NETRookie
NETRookie

Reputation: 86

Try this! 

select  
lower(convert(varchar(10), GETDATE(), 101) +
' '+ 
LEFT((substring(convert(varchar(20), GETDATE(), 22),9,Len(GETDATE())+1)), 6)
+
(RIGHT(substring(convert(varchar(20), GETDATE(), 22),9,Len(GETDATE())+1),2)))

Upvotes: 1

GotaloveCode
GotaloveCode

Reputation: 1034

Try the below

 SELECT CAST(DATEPART(DD,GETDATE()) AS VARCHAR)+'/'
+CAST(DATEPART(MM,GETDATE()) AS VARCHAR)
+'/'+CAST(DATEPART(YYYY,GETDATE()) AS VARCHAR)
+' '+CAST(DATEPART(HH,GETDATE()) AS VARCHAR)
+':'+CAST(DATEPART(MI,GETDATE()) AS VARCHAR)

also look at this Custom Date/Time formatting in SQL Server

Upvotes: 4

Related Questions