Get all date from 4 digit - 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

Transact-SQL (2005)

Get all date from 4 digit

Author

Topic

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-02 : 01:54:08

Hi, i was sleepy and just now i saw an OP ask retrieve week...i wanna ask is it posible to get whole year date from just 4 digit? i google it but can't find any infor that can help

AMITOFO+AMEN+YA ALLAH Hope the query works

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-02 : 01:55:09

datepart(year, thedate)

year(thedate)

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-02 : 01:58:19

hm..i mean that is...example i key in 1900 into a function...it will show the whole year date from 01/01/00-31/12/00
(365 rows affected)
AMITOFO+AMEN+YA ALLAH Hope the query works

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-02 : 02:02:17

one quick way, use this F_TABLE_DATE
or number table F_TABLE_NUMBER_RANGE to generate the date

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-02 : 02:08:55

wow since when you touch sql...knows so much of thing!

AMITOFO+AMEN+YA ALLAH Hope the query works

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-02 : 03:07:29

If you just want the starting date and the ending date,

DECLARE	@theYear SMALLINT

SET	@theYear = 2007

SELECT	DATEADD(YEAR, @theYear - 1900, 0),
	DATEADD(YEAR, @theYear - 1899, -1)

Microsoft SQL Server MVP

N 56°04'39.26"
E 12°55'05.63"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-02 : 03:10:54

declare @year int

set @year=2000

select dateadd(year,@year-1900,dateadd(day,number-1,0)) from
(
select row_number () over (order by name) as number from sysobjects
) as t
where year(dateadd(year,@year-1900,dateadd(day,number-1,0)) )=@year

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-02 : 03:11:04

quote:
Originally posted by waterduck

wow since when you touch sql...knows so much of thing!

AMITOFO+AMEN+YA ALLAH Hope the query works

Actually not that long compare to others. I only started "touching" MS SQL Server since 2003.

Stick around SQLTeam. You will learn much more from anywhere else.

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-02 : 03:24:02

6 Years!!! i must learn all urs knowlegde within 1 year

AMITOFO+AMEN+YA ALLAH Hope the query works

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-02 : 03:29:18

quote:
Originally posted by waterduck

6 Years!!! i must learn all urs knowlegde within 1 year

AMITOFO+AMEN+YA ALLAH Hope the query works

OK. I will do a database backup and FTP to you

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-02 : 03:30:58

i wonder peso get his salary raise for his mvp?

AMITOFO+AMEN+YA ALLAH Hope the query works

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-02 : 03:36:39

quote:
Originally posted by waterduck

i wonder peso get his salary raise for his mvp?

AMITOFO+AMEN+YA ALLAH Hope the query works

It depends

Madhivanan

Failing to plan is Planning to fail

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-02 : 03:38:10

haha..so he planing to treat us?if not...i think....rollback

AMITOFO+AMEN+YA ALLAH Hope the query works

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-02 : 03:38:15

Madhi, I am missing the leapday of February 29th

declare @year int

set @year = 1904

select	distinct
	dateadd(year, @year - 1904, number) from 
	(
		select	row_number() over (order by name) + 1459 as number
		from	sysobjects
	) as t
where	year(dateadd(year, @year - 1904, number)) = @year

Microsoft SQL Server MVP

N 56°04'39.26"
E 12°55'05.63"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-02 : 03:50:14

Yes. Thanks

Madhivanan

Failing to plan is Planning to fail

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-02 : 03:53:16

means what?

AMITOFO+AMEN+YA ALLAH Hope the query works

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-02 : 03:58:52

quote:
Originally posted by waterduck

means what?

AMITOFO+AMEN+YA ALLAH Hope the query works

run both query (Madhi & Peso), set the same year @year = 1904 , compare and see the different ?

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-02 : 04:29:01

>"< i can't understand why madhi query left that day

AMITOFO+AMEN+YA ALLAH Hope the query works

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-02 : 04:33:14

because his based year is 1900. 1900 is not a leap year. For the month of Feb 1900, there isn't a Feb 29.
so the following will not have a Feb 29 for the selected year (@year)

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

the section in bold is basically adding number to date 0. Date 0 is 1900-01-01

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-02 : 04:35:30

dateadd(year, @year-1900, dateadd(day, number - 1, 0))
Bold = isn't here convert back to 1904?

AMITOFO+AMEN+YA ALLAH Hope the query works

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-02 : 04:39:35

quote:
Originally posted by waterduck

dateadd(year, @year-1900, dateadd(day, number - 1, 0))
Bold = isn't here convert back to 1904?

AMITOFO+AMEN+YA ALLAH Hope the query works

That's the idea. If the required date is 1904, @year = 1904

1904 - 1900 = 4. So you add 4 years to the date in blue

KH
[spoiler]Time is always against us[/spoiler]

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources