CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Mr John
Mr John

Reputation: 241

Concatenate DATEADD & DATEPART

SQL Server 2008. Is there a way to concatenate the below and drop the 00:00:00:000?

*Edit - fulldate column contains a list of dates

SELECT DISTINCT
    DATEADD(dd, -(DATEPART(dw, fulldate)-1), fulldate) [WeekStart], 
    DATEADD(dd, 7-    (DATEPART(dw, fulldate)), fulldate) [WeekEnd]
FROM time
WHERE YEAR(time) >= 2016
ORDER BY WeekStart

Results:

WeekOf

12-27-2015-01-02-2016

Etc...

Upvotes: 0

Views: 881

Answers (1)

Andrey Korneyev
Andrey Korneyev

Reputation: 26856

Since dateadd returns date not string - you have to convert your values to strings first using appropriate format (110 in your case) and then concatenate them:

select distinct
    convert(nvarchar(20), DATEADD(dd, -(DATEPART(dw, fulldate)-1), fulldate), 110) + '-' + 
    convert(nvarchar(20), DATEADD(dd, 7-    (DATEPART(dw, fulldate)), fulldate), 110) as WeekOf
from time
where YEAR(time) >= 2016
order by WeekStart

Upvotes: 1

Related Questions