R S SAINI R S SAINI - 1 month ago 8
SQL Question

date format convert into varchar dd-mmm-yy format in sql server 2012

today's date is 2016-08-23 in DATE format
and i want covert into a specific format like 1st Aug to 31st Aug, 2016 in VARCHAR format into sql.




main purpose is i want first and last date of month in this specific format using with sql procedure or function.


so what should i do?

Answer

Try with the below query..

DECLARE @testdate DATETIME
SELECT @testdate = '2016-08-23'

SELECT LEFT (CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@testdate)-1),@testdate),113),6)  
+' to '+LEFT(CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@testdate))),
 DATEADD(mm,1,@testdate)),113),11) AS Date_Value
Comments