Solved

# Beginning of year

Posted on 2004-11-19
1,081 Views
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
Question by:lrbrister

LVL 16

Expert Comment

Beginning of current year:

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

Is that what you wanted?
0

LVL 6

Accepted Solution

declare @string varchar(100)
select @string = datepart(yyyy,getdate())
select DateDiff(dd, '01-01-'+@string, getdate())

Duane
0

LVL 16

Expert Comment

Good afternoon, by the way, where I am!
0

Author Comment

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

LVL 16

Expert Comment

Duane's code gives you the day number in the year. But you can get this more directly with:

DATEPART(dayofyear, GETDATE())
0

LVL 16

Expert Comment

This is a fragment, not a statement. Stick a SELECT in front of it to see it working.
0

LVL 16

Expert Comment

... technically an "expression", I guess!
0

LVL 2

Expert Comment

'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

Author Comment

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

LVL 6

Expert Comment

lrbrister

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

Dua
0

LVL 16

Expert Comment

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

## Featured Post

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.