T-SQL 2 tips:1. Calculate any two dates between " Monday " to " Sunday " each have a few! 2. According to birth.

These two tips, do not write do not know, a write scare!
Are seemingly simple, actually do up on the Meng, have to think carefully, can write right!
All have the date operation of the program should be careful Oh!

Let's say the second one:

2. Calculate the age accurately according to the date of birth!
The so-called calculation of the exact age is: birthday is not a day to grow one year old!
Everyone commonly used, the number of years counted as age! If the demand to be accurate, such as: insurance and so on, it is thick!
Of course, can also be extended to the basis of the date of the calculation of accurate Siling, to calculate the demand for a raise!
At first I thought it was very simple, and I wrote it several times before I wrote it! The Masters are also being swayed by me several times near fainting!
Do not believe that the year 2002-11-27 16:16:26 posts for the card:

There are fields in my table. Date of birth, how can I calculate the age of the person according to the current date and fill in the corresponding field in the table?
http://search.csdn.net/expert/topicview1.asp?id=1210302

The book belongs to the story:

DECLARE @Birthday smalldatetime
Set @Birthday = ' 1949-6-5 '

--use more than one date variable, in order to expand: To calculate the future or a moment in time of the exact age of the function!
DECLARE @ smalldatetime
SET @ = GETDATE ()

Select DateDiff (year, @Birthday, @)
+ Case when DateAdd (Year,datediff, @Birthday, @), @Birthday) <= @
Then 1
else 0
End

Again the first one:
1. Calculate the number of Monday to Sunday between any two dates!
The cause comes from the sticker:
The SQL statement asks for the number of days to work in the month
http://community.csdn.net/Expert/TopicView1.asp?id=3291510

Originally want to lazy Google, no satisfactory results, as if he had no bottom! ideas are different!
"To find a tart algorithm" (a bit of the same feeling, simple to say, do it dizzy)
Http://www.itpub.net/252645.html

By the way, I write the program always want to write "more general" some, may be wrong!


Because @ @datefirst can cause datepart (weekday,[date]) uncertainty through set Datefirst N settings!
So with datename!
Because the determination of the date is the weeks will certainly never change! Of course not with datefirst change!
So it's better to use datename!.
In addition, according to the Chinese habit: Sunday counts as last week's final day!
If it is a different language version of SQL Server, note the format of the week in the language version!

DECLARE @b datetime
DECLARE @e datetime

Set @b = ' 2004-07-29 '
Set @e = ' 2004-08-05 '

Select @b as Start date, @e as end date,
DateDiff (Week
, Case when datename (weekday,@b) = ' Sunday ' then @b-1
else @b End
, Case when datename (weekday,@e) = ' Sunday ' then @e-1
else @e End
) + 1 as span of weeks

, DateDiff (week
, Case when datename (weekday,@b) = ' Sunday ' then @b-1
else @b End
, Case when datename (weekday,@e) = ' Sunday ' then @e-1
else @e End
) + 1

-Case of Datename (weekday,@b) in (' Tuesday ', ' Wednesday ', ' Thursday ', ' Friday ', ' Saturday ', ' Sunday ') then 1
else 0 End
As Monday number

, DateDiff (week
, Case when datename (weekday,@b) = ' Sunday ' then @b-1
else @b End
, Case when datename (weekday,@e) = ' Sunday ' then @e-1
else @e End
) + 1

-Case of Datename (weekday,@b) in (' Wednesday ', ' Thursday ', ' Friday ', ' Saturday ', ' Sunday ') then 1
else 0 End
-Case of Datename (weekday,@e) in (' Monday ') then 1
else 0 End
As Tuesday number

, DateDiff (week
, Case when datename (weekday,@b) = ' Sunday ' then @b-1
else @b End
, Case when datename (weekday,@e) = ' Sunday ' then @e-1
else @e End
) + 1

-Case of Datename (weekday,@b) in (' Thursday ', ' Friday ', ' Saturday ', ' Sunday ') then 1
else 0 End
-Case of Datename (weekday,@e) in (' Monday ', ' Tuesday ') then 1
else 0 End
As Wednesday number

, DateDiff (week
, Case when datename (weekday,@b) = ' Sunday ' then @b-1
else @b End
, Case when datename (weekday,@e) = ' Sunday ' then @e-1
else @e End
) + 1

-Case of Datename (weekday,@b) in (' Friday ', ' Saturday ', ' Sunday ') then 1
else 0 End
-Case of Datename (weekday,@e) in (' Monday ', ' Tuesday ', ' Wednesday ') then 1
else 0 End
As Thursday number

, DateDiff (week
, Case when datename (weekday,@b) = ' Sunday ' then @b-1
else @b End
, Case when datename (weekday,@e) = ' Sunday ' then @e-1
else @e End
) + 1

-Case of Datename (weekday,@b) in (' Saturday ', ' Sunday ') then 1
else 0 End
-Case of Datename (weekday,@e) in (' Monday ', ' Tuesday ', ' Wednesday ', ' Thursday ') then 1
else 0 End
As Friday number

, DateDiff (week
, Case when datename (weekday,@b) = ' Sunday ' then @b-1
else @b End
, Case when datename (weekday,@e) = ' Sunday ' then @e-1
else @e End
) + 1

-Case of Datename (weekday,@b) in (' Sunday ') then 1
else 0 End
-Case of Datename (weekday,@e) in (' Monday ', ' Tuesday ', ' Wednesday ', ' Thursday ', ' Friday ') then 1
else 0 End
As Saturday number

, DateDiff (week
, Case when datename (weekday,@b) = ' Sunday ' then @b-1
else @b End
, Case when datename (weekday,@e) = ' Sunday ' then @e-1
else @e End
) + 1

-Case of Datename (weekday,@e) in (' Monday ', ' Tuesday ', ' Wednesday ', ' Thursday ', ' Friday ', ' Saturday ') then 1
else 0 End

As Sunday number

--Wow, finally got it done, at first did not think of is: unexpectedly really moved a brain!

The rest is the use of people can do whatever it takes to become a UDF SP (View) and so on!
If you really understand my thinking, you can use any language to achieve!

And finally, the ad:
Http://www.microshaoft.com
Http://www.csdn.net/Develop/list_article.asp?author=playyuer

Send you a few big long SQL, perhaps ruthless useful, click, help me add a bit of popularity:

T-SQL generates two new true Gregorian calendars
http://www.csdn.net/Develop/Read_Article.asp?Id=26447


T-SQL generates a simple Gregorian calendar T-SQL contains the date of the month and the Year of the week
http://www.csdn.net/Develop/Read_Article.asp?Id=26083


Http://www.microshaoft.com
Http://www.csdn.net/Develop/list_article.asp?author=playyuer