Click here to Skip to main content
11,709,844 members (48,828 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL Excel
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
Comments
Maciej Los at 8-Apr-13 5:36am
   
SQL or Excel? I'm a bit confused...
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Use daytime and the add function instead:
http://msdn.microsoft.com/en-us/library/system.datetime.add.aspx[^]
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

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
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 514
1 OriginalGriff 250
2 Peter Leow 229
3 Maciej Los 185
4 Mika Wendelius 175
0 OriginalGriff 9,428
1 Sergey Alexandrovich Kryukov 9,037
2 CPallini 5,189
3 Maciej Los 4,991
4 Mika Wendelius 3,856


Advertise | Privacy | Mobile
Web01 | 2.8.150819.1 | Last Updated 8 Apr 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100