Click here to Skip to main content
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 18: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)



Advertise | Privacy | Mobile
Web03 | 2.8.141220.1 | Last Updated 8 Apr 2013
Copyright © CodeProject, 1999-2014
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