CODEWITHSUNDEEP
sqlsql-server
Nayana
Nayana

Reputation: 1549

How to print a week in date in SQL

I use sql server. what I've been doing is this:

LEFT(yearmonth,4) + RIGHT( '0' + CONVERT(VARCHAR, DATEPART(WK, yearmonthdate)), 2)

yearmonth = '201601'

and

yearmonthdate = '20160101' through '20160131'

which prints out like this: 

201601
201602

but I want to print out like the following: 

20160101-20160102
20160103-20160109

respectively.

How do I accomplish that? I've on google but I couldn't get to print out like that. Thank you in advance.

Upvotes: 0

Views: 96

Answers (1)

Manjunathv111223
Manjunathv111223

Reputation: 36

DECLARE @Table TABLE (Col1 INT, Col2 DATETIME)
DECLARE @StartDT DATETIME
DECLARE @tempDT DATETIME
DECLARE @EndDT DATETIME
SET @StartDT  = DATEFROMPARTS(YEAR(getdate()),MONTH(getdate()),1)
SET @EndDT  = EOMONTH (@StartDT)

set @tempDT=@StartDT

WHILE @StartDT <  @EndDT
BEGIN
PRINT 
     CONVERT(VARCHAR,cast(@tempDT as date)) 
     +  ' - '   + 
     convert(VARCHAR,cast(DATEADD(dd, 7-(DATEPART(dw, @StartDT)), @StartDT) as date))

SET @StartDT    =   DATEADD(dd, 7-(DATEPART(dw, @StartDT)), @StartDT)
SET  @tempDT = DATEADD(dd,1,@StartDT)
SET @StartDT    =   DATEADD(WEEK,1,@StartDT)
END

PRINT   
     CONVERT(VARCHAR,cast(@tempDT as date)) 
     +  ' - '   + 
     convert(VARCHAR,cast(@EndDT as date))

Upvotes: 1

Related Questions