http://blog.9cbs.net/playyuer/archive/2004/08/20/7969.aspx
These two tips, do not know, write is scared! It is a seemingly simple, actually doing it, you have to think about it, you can write it! Any procedure with date operation must be careful!
Let's talk about the second:
2. According to the date of birth! The so-called calculation accurate age is: the birthday is not one year old! Everyone is common, the number of intervals is counted as age! If the demand is accurate, such as: Insurance, it is rough! Of course! Of course! Of course! Of course! Of course! Of course! Of course! Of course! Of course! Of course! Of course! Of course! Of course! Of course! Of course! Of course! Of course! Of course It can also be deducted to calculate the precise, the needs of the inquiry date, calculate the needs of the salary increase! I originally thought it was very simple. I also wrote a few times in the year! The masters were also shackled several times several times. Pour! Do not believe in 2002-11-27 16:16:26 Post:
"I have a field in my table. I have to calculate the age of the person according to the current date, and fill it into the corresponding field of the table?" Http://search.9cbs.net/expert/topicview1.asp?id = 1210302
Book is positive:
Declare @BIRTHDAY SmallDatetimeSet @BIRTHDAY = '1949-6-5'
- Use a date variable, in order to expand: calculate the function of the precise age at the time of the future or in the past! Declare @ SmallDateTimeSet @ = getdate ()
Select Datediff (Year, @ Birthday, @) Case When Dateadd (Year, Datediff (Year, @ Birthday, @), @ Birthday) <= @ Then 1 Else 0 End
Besides the first: 1. Calculate each of the two days of Monday to Sunday, there are several! The cause is from this post: "SQL statement seeks the number of days of the day" http://community.9cbs.net/expert/ TopicView1.asp? Id = 3291510
Originally wanted to lazy google, there is no satisfaction, it seems that he will not have bottom! Thinking is different! "Ask a bit of algorithm" (a bit of a feeling, simply, and doing it) http: // www. ITPub.Net/252645.html
By the way, I have written the program to always want to write "more universal", I may answer the question!
Since @@ DateFirst can make DatePart (WeekDay, [DATE]) to make DatePart (WeekDay, [DATE])! So use DateName! Datename! In addition to the Chinese habits: Sunday counts last day last week! If it is other language version SQL Server pays the form of the language version!
Declare @B DateTimedeclare @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' Ten @E - 1 Else @e End 1 AS cross Week
, Datediff (Week, Case When Datename (Weekday, @ b) = 'Sunday' Then @B - 1 else @B end, Case When Datename (Weekday, @ e) = 'Sunday' Ten @E - 1 else @E end ) 1- Case When Datename (Weekday, @ b) in ('Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday') THEN 1 ELSE 0 Endas Monday
, Datediff (Week, Case When Datename (Weekday, @ b) = 'Sunday' Then @B - 1 else @B end, Case When Datename (Weekday, @ e) = 'Sunday' Ten @E - 1 else @E end 1
- Case When Datename (Weekday, @ b) in ('Wednesday', '' '' '' '' '' '' '', ',', ',' Monday ') THEN 1 ELSE 0 Endas Tuesday
, Datediff (Week, Case When Datename (Weekday, @ b) = 'Sunday' Then @B - 1 else @B end, Case When Datename (Weekday, @ e) = 'Sunday' Ten @E - 1 else @E end 1
- Case When Datename (Weekday, @ b) in ('Thursday', 'Friday ",' Saturday ',' Sunday ') THEN 1 Else 0 End- Case When Datename (Weekday, @ e) in (' Monday", 'Tuesday') THEN 1 ELSE 0 Endas Wednesday
, Datediff (Week, Case When Datename (Weekday, @ b) = 'Sunday' Then @B - 1 else @B end, Case When Datename (Weekday, @ e) = 'Sunday' Ten @E - 1 else @E end ) 1- Case When Datename (Weekday, @ b) in ('Friday', 'Saturday ",' Sunday ') THEN 1 ELSE 0 End- Case When Datename (Weekday, @ e) in (' Monday" Tuesday ',' Wednesday ') THEN 1 ELSE 0 Endas Thursday
, Datediff (Week, Case When Datename (Weekday, @ b) = 'Sunday' Then @B - 1 else @B end, Case When Datename (Weekday, @ e) = 'Sunday' Ten @E - 1 else @E end 1
- Case When Datename (Weekday, @ b) in ('Saturday ",' Sunday ') Then 1 Else 0 End- Case When Datename (Weekday, @ e) in (' Monday ',' Tuesday ',' Wednesday", 'Thursday') THEN 1 ELSE 0 Endas Friday
, Datediff (Week, Case When Datename (Weekday, @ b) = 'Sunday' Then @B - 1 else @B end, Case When Datename (Weekday, @ e) = 'Sunday' Ten @E - 1 else @E end 1
- Case When Datename (Weekday, @ b) in ('Sunday') THEN 1 Else 0 end- Case When Datename (Weekday, @ e) in ('Monday', 'Tuesday', 'Wednesday ",' Thursday ', 'Friday') THEN 1 ELSE 0 Endas Six numbers
, Datediff (Week, Case When Datename (Weekday, @ b) = 'Sunday' Then @B - 1 else @B end, Case When Datename (Weekday, @ e) = 'Sunday' Ten @E - 1 else @E end 1- Case When Datename (Weekday, @ e) in ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday') THEN 1 ELSE 0 End
AS Sunday
- Wow finally got it, and did not expect it: "I actually took a brain!
The rest is that people can turn him into UDF SP (View) for what they want! If you really understand my ideas, you can use any language!
Finally, make an advertisement: http://www.microshaoft.comHttp: //www.9cbs.net/develop/list_article.asp? Author = Playyuer
Send you a few giant SQL, maybe it is useful, click, help me add some popularity:
T-SQL generates two new real gift years http://www.9cbs.net/develop/read_article.asp?id=26447
T-SQL generates a simple Queen annual calendar T-SQL with the date of the date and years http://www.9cbs.net/develop/read_article.asp?id=26083
http://www.microshaoft.com ccttp://www.9cbs.net/develop/list_article.asp? Author = Playyuer
