12,406,795 members (63,132 online)
Rate this:
See more:
Hi guys any one can sugest me.

I'm using sql functions to fill date and day in the excel

In my user interface i have From date and To date.
1 April 2013--> From Date
30 April 2013-->To Date

Excel Report

Date Day
1-03-13 Monday

2-03-13 Tuesday
3-03-13 Wednesday
.
15-03-13 Tuesday
..
30-03-13 Tuesday

Here day calculation i made it excel sheet formula itself.

=TEXT(A1, "dddd")

In My excel sheet i hard coded the formula for 31 days it is static

But i my user interface he may select less than 31 days

i.e 1-3-12 to 15-3-12

So i my excel sheet Day column after 15-03-13 all coming Saturday,Saturday only

How to solve this problem.

Bug Result:
Date Day
1-03-13 Monday

2-03-13 Tuesday
3-03-13 Wednesday
.
15-03-13 Tuesday
Saturday
Saturday
Saturday
Expected Result
Date Day
1-03-13 Monday

2-03-13 Tuesday
3-03-13 Wednesday
.
15-03-13 Tuesday
Posted 7-Apr-13 17:13pm
HRVP360
Maciej Los 8-Apr-13 5:36am

SQL or Excel? I'm a bit confused...

Rate this:

Rate this:

## Solution 2

Excel (VBA):
```Sub EnumDates()

Dim curDate As Date, fromDate As Date, toDate As Date
Dim i As Integer

i = 1
fromDate = DateSerial(2013, 1, 1)
toDate = DateSerial(2013, 3, 31)
curDate = fromDate - 1
Do While curDate < toDate
curDate = curDate + 1
With ThisWorkbook.Worksheets(1).Range("A" & i)
.NumberFormat = "yyyy-MM-dd"
.Value = curDate
End With
With ThisWorkbook.Worksheets(1).Range("B" & i)
.NumberFormat = "dddd"
.Value = curDate
End With
i = i + 1
Loop

End Sub```

SQL:
```DECLARE @curDate DATETIME
DECLARE @fromDate DATETIME
DECLARE @toDate DATETIME

SET @fromDate = '2013-01-01'
SET @toDate = '2013-03-31'
SET @curDate = @fromDate -1
;WITH dates AS
(
SELECT @curDate +1 As MyDate, DATENAME(dw, @curDate +1) AS MyDayName
UNION ALL
SELECT MyDate +1, DATENAME(dw, MyDate +1) AS MyDayName
FROM dates
WHERE MyDate<@toDate
)
SELECT *
FROM dates```

Top Experts
Last 24hrsThis month
 OriginalGriff 270 Karthik Bangalore 220 Richard MacCutchan 180 Afzaal Ahmad Zeeshan 75 ppolymorphe 40
 OriginalGriff 7,957 Karthik Bangalore 3,531 ppolymorphe 3,420 Richard MacCutchan 2,652 F-ES Sitecore 2,300