Days in a Year

# Thread: Days in a Year

1. Member
Join Date
Dec 1969
Posts
37

## Days in a Year

I&#039;m using the code below to find the First and Last days of the year and the number of days in a year, but it seems a bit long handed. In access I have used the DateSerial function but can&#039;t find an SQL equivalent.<BR><BR>Set @strYear=&#039;2003&#039; --This will be passed as a Variable<BR>set @strD1= @strYear + &#039;/01/01&#039;<BR>set @strD2= @strYear + &#039;/12/31&#039;<BR>Set @d1=CAST(@strD1 as DateTime)<BR>Set @d2=CAST(@strD2 as DateTime)<BR>set @intDaysInYear = (DateDiff(day, @d1, @d2) + 1)<BR><BR>Thanks in Advance.

2. Senior Member
Join Date
Dec 1969
Posts
11,247

## RE: Days in a Year

Actually that will give you 364 instead of 365!<BR><BR>What you want is <BR>select Datediff(d, &#039;1/1/2003&#039;, &#039;1/1/2004&#039;)

3. Senior Member
Join Date
Dec 1969
Posts
11,247

## So in SQL

To find the number of days in the CURRRENT Year<BR><BR>select Datediff(day, Convert(datetime, &#039;1/1/&#039; + convert(char(4), Year(getdate()))), convert(datetime, &#039;1/1/&#039; + convert(char(4), Year(getdate())+1)))<BR><BR>And dynamically<BR><BR>select Datediff(day, Convert(datetime, &#039;1/1/&#039; + convert(char(4), Year(" & stryear & "))), convert(datetime, &#039;1/1/&#039; + convert(char(4), Year(" & stryear & ")+1)))

4. Senior Member
Join Date
Dec 1969
Posts
96,118

## Nope...gotcha again...

&#062; Actually that will give you 364 instead of 365<BR><BR>Nope. Read his code again. He added +1 to the DateDiff:<BR><BR> set @intDaysInYear = (DateDiff(day, @d1, @d2) + 1) <BR><BR>But I do like your solution better.<BR><BR>

5. Senior Member
Join Date
Dec 1969
Posts
96,118

## Welllll...

...that&#039;s really pretty silly! The "dynamically" part, that is.<BR><BR>If you are going to use VBScript in any way to build the SQL query, then you don&#039;t need any of that crap, anyway!<BR><BR>&#060;%<BR>SQL = "SELECT DateDiff(day,&#039;1/1/" & someYear & "&#039;,&#039;1/1/" & (someYear+1) & "&#039;)"<BR>%&#062;<BR>BUT...if you are going to do *that*, then why would you use SQL, at all???<BR>&#060;%<BR>daysInYear = DateSerial(someYear+1,1,1) - DateSerial(someYear,1,1)<BR>%&#062;<BR><BR>Or, if you are going to pass in the year as a parameter to the SQL, you *could* do:<BR><BR> select Datediff(day, Convert(datetime, &#039;1/1/&#039; + convert(char(4), @yr), <BR> Convert(datetime, &#039;1/1/&#039; + convert(char(4), (@yr+1) ) <BR> )<BR><BR>But if you are going to do *THAT* then why not just<BR><BR> Set @daysInYr = DateDiff(day, @jan1, DateAdd(year, 1, @jan1) )<BR><BR>and then pass in @jan1 as 1/1/1998 or whatever. Though, again, if you are going to pass in something artificial, like 1/1/1998, then why not pass in the number of days and not bother with SQL doing it, at all.<BR><BR>**********************<BR><BR>Finally, your scheme for number of days in current year is fine.<BR><BR>[Well, I had to surprise you a *LITTLE* bit.]<BR><BR>

6. Senior Member
Join Date
Dec 1969
Posts
11,247

## RE: Welllll...

I agree with you about the dynamic But HEY IT IS FRIDAY! and I was bored here so I figured I&#039;d try it<BR><BR>oh thanks for the last sentence! {makes my day}

7. Senior Member
Join Date
Dec 1969
Posts
11,247

## Yep! got me Guv <nt>

.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•