• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1099
  • Last Modified:

Beginning of year

Good morning,
  I need to be able to calculate from the beginning of the current year every year to get year to date information.

What is the SQL statement for this.

I'll be placeing ion a SP or DTS SQl statement.
Thanks,
lrbrister
0
lrbrister
Asked:
lrbrister
  • 6
  • 2
  • 2
  • +1
1 Solution
 
muzzy2003Commented:
Beginning of current year:

CAST(CAST(YEAR(GETDATE()) AS varchar(10)) + '-01-01' AS datetime)

Is that what you wanted?
0
 
Duane LawrenceCommented:
declare @string varchar(100)
select @string = datepart(yyyy,getdate())
select DateDiff(dd, '01-01-'+@string, getdate())

Duane
0
 
muzzy2003Commented:
Good afternoon, by the way, where I am!
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
lrbristerAuthor Commented:
muzzy2003,
I tried your statement in Query Analyzer and got the error message below


CAST(CAST(YEAR(GETDATE()) AS varchar(10)) + '-01-01' AS datetime)

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'CAST'.
0
 
muzzy2003Commented:
Duane's code gives you the day number in the year. But you can get this more directly with:

DATEPART(dayofyear, GETDATE())
0
 
muzzy2003Commented:
This is a fragment, not a statement. Stick a SELECT in front of it to see it working.
0
 
muzzy2003Commented:
... technically an "expression", I guess!
0
 
acharbonneauCommented:
'muzzy2003,
'I tried your statement in Query Analyzer and got the error message below

'CAST(CAST(YEAR(GETDATE()) AS varchar(10)) + '-01-01' AS datetime)

'Server: Msg 170, Level 15, State 1, Line 1
'Line 1: Incorrect syntax near 'CAST'.



You can't just evaluate the expression, you need to do something with the result of it.

try this:

            print  CAST(CAST(YEAR(GETDATE()) AS varchar(10)) + '-01-01' AS datetime)

0
 
lrbristerAuthor Commented:
duanelawrence,
  Yours is what I'm looking for right now. Points being awarded now.

 It appears that the '01-01' joins itself to the @string  for the beginning of any year and gets the difference between itself and the getdate() statement...correct?

I have to do a lot of annualization and that number is what I need in my calculations.

Thanks.
0
 
Duane LawrenceCommented:
lrbrister

Yes, that is correct.  I should have put a comment in to explain what I was doing.  Thanks for the points.

Dua
0
 
muzzy2003Commented:
So isn't DATEPART(dayofyear, GETDATE()) what you want then?
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 6
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now