date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

date

Author

Topic

wided
Posting Yak Master

218 Posts

Posted - 2010-02-18 : 09:03:37

Sorry,

I want to find the query to have a date when i have only the year and the number od day in the year
thanks

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-18 : 09:09:50

Query for that as i understood is below

DECLARE @Year INT
Declare @Day INT
SET @Year = 2010
SET @Day = 4

SELECT DATEADD(DAY, @Day-1, '01-Jan-' + CAST(@Year AS VARCHAR))

Vabhav T

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-18 : 09:14:27

or

select dateadd(year,@year-1900,dateadd(day,@day-1,0))

Madhivanan

Failing to plan is Planning to fail

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-18 : 09:16:58

Yes thats better but mine was more readable
anyways....

Vabhav T

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 09:24:31

quote:
Originally posted by vaibhavktiwari83

Yes thats better but mine was more readable
anyways....

Vabhav T

Madhi's solution is more efficient

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 09:27:38

dont cross post

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=140071

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-02-18 : 12:03:12

"but mine was more readable"

Hmmm ... true. But it involves data conversions (which are relatively slow):

CAST(@Year AS VARCHAR) - Number to string

'01-Jan-' + CAST(@Year AS VARCHAR) - String concatenation

then as used as a parameter in DATEADD and implicit conversion to DATETIME. SQL has to work out that '01-Jan-2010' is formatted as d/m/y and what month JAN coresponds to - all lots of SQL cycles.

And "JAN" it only works in English of course (well, probably some other European language, but not Italian:


SET LANGUAGE Italian

DECLARE @Year INT
Declare @Day INT
SET @Year = 2010
SET @Day = 4

SELECT DATEADD(DAY, @Day-1, '01-Jan-' + CAST(@Year AS VARCHAR))

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-19 : 01:19:08

quote:
Originally posted by vaibhavktiwari83

Yes thats better but mine was more readable
anyways....

Vabhav T

See the replies posted by Visakh and Kristen
You must have understood now

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources