0

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.

C Sharper
  • 8,284
  • 26
  • 88
  • 151

3 Answers3

4

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

Community
  • 1
  • 1
GotaloveCode
  • 994
  • 1
  • 13
  • 30
1

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)))
NETRookie
  • 86
  • 6
1
declare @date datetime

select @date = GetDate()

select convert(nvarchar, @date, 101) + ' ' + right(convert(nvarchar, @date, 100), 6)
David Brabant
  • 41,623
  • 16
  • 83
  • 111