Help a T-SQL Newbie

Question

Help a T-SQL Newbie

varunkum

Default port

Points: 1479
More actions
April 7, 2011 at 8:12 am

#248503

I need SQL to display all the days in a given week( in the week of getdate()). Do we need any sort of counter ? Someone please help.
select to_char(trunc(sysdate, 'iw') + level ,'day') from dual
connect by level <= 7
This is oracle, so simple. I need something like this in T-SQL.
I am using SQL server 2008. I am a newbie forgive my ignorance.
April 7, 2011 at 8:30 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

varunkum Default port Points: 1479 More actions · Answer 1

Thank you so much. This helps. But i see SQL is too big as compared with Oracle. Could you also please help display the dates as Monday,Tuesday,....sunday or atleast Mon,Tue,...Sun.

LOOKUP_BI-756009 SSCertifiable Points: 7286 More actions · Answer 2

What about this ?

DECLARE @i INT

SET @i =0

WHILE (@i <=6)

BEGIN

SELECT DATENAME(DW,DATEADD(dd,+@i,GETDATE())) + ' ,' + CONVERT(VARCHAR(50),DATEADD(DD,+@i,GETDATE()),101)

SET @i=@i+1

END

Results as

Thursday ,04/07/2011

Friday ,04/08/2011

and ....

Cadavre SSC-Forever Points: 41690 More actions · Answer 3

WITH weekdays

AS (SELECT CONVERT(DATETIME, Dateadd(dd, -Datepart(dw, Getdate()), Getdate())+1) AS startofweek,

CONVERT(DATETIME, Dateadd(dd, -Datepart(dw, Getdate()), Getdate())+7) AS endofweek

UNION ALL

SELECT Dateadd(dd, 1, startofweek), endofweek

FROM weekdays

WHERE startofweek < endofweek)

SELECT DATENAME(DW,startofweek) + ', ' + CONVERT(VARCHAR(50),startofweek,101)

FROM weekdays

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

varunkum Default port Points: 1479 More actions · Answer 4

varunkum

Default port

Points: 1479

April 7, 2011 at 8:49 am

#1308590

This is helpful.Thank you.

Cadavre SSC-Forever Points: 41690 More actions · Answer 5

And just in case you intend to do all of your date formatting in SQL (it's a presentation layer task imo, so should be done in the front end code instead)

WITH weekdays

AS (SELECT CONVERT(DATETIME, Dateadd(dd, -Datepart(dw, Getdate()), Getdate())+1) AS startofweek,

CONVERT(DATETIME, Dateadd(dd, -Datepart(dw, Getdate()), Getdate())+7) AS endofweek

UNION ALL

SELECT Dateadd(dd, 1, startofweek), endofweek

FROM weekdays

WHERE startofweek < endofweek)

SELECT DATENAME(DW,startofweek) + ', ' + CONVERT(VARCHAR(2),Datepart(DAY,startofweek)) +

CASE WHEN Datepart(DAY,startofweek) = 1

THEN 'st'

WHEN Datepart(DAY,startofweek) = 2

THEN 'nd'

WHEN Datepart(DAY,startofweek) = 3

THEN 'rd'

ELSE 'th' END + ' ' + CONVERT(VARCHAR(20),DATENAME(MONTH,startofweek)) + ' ' + CONVERT(VARCHAR(4),Datepart(YEAR,startofweek))

FROM weekdays

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 6

How abt this?

DECLARE @Date DATETIME = GETDATE()

DECLARE @DatePart INT = DATEPART(WEEKDAY , @DATE)

; WITH Numbers (N) AS

(

SELECT 1

UNION ALL SELECT 2

UNION ALL SELECT 3

UNION ALL SELECT 4

UNION ALL SELECT 5

UNION ALL SELECT 6

UNION ALL SELECT 7

),

DaysOfWeek AS

(

SELECT DATEADD(DAY , ( N - @DatePart) , @Date) DayOfThisWeek

FROM Numbers

)

SELECT DATENAME(DW,DayOfThisWeek) + ', ' + CONVERT(VARCHAR(50),DayOfThisWeek,103) DayInWords

FROM DaysOfWeek