Everything about database

Normally,You need to win current date and consideration a few other date,For example,The first day when your program may need to judge a month or the last day.Your much National People's Congress knows how to have date breaking up without exception (year, month, day) ,Use merely next break up come out year, the month, day computation in be being put in a few function gives the date that he needs!In this article,My general tells you how to use DATEADD and DATEDIFF function to calculate a few different date that you should use the possibility in giving the order in you.
Before the example in use article,You must note the following problem.Major likelihood is not all example the outcome that carries out on different machine may be different,This is complete by the first days be a week which day this setting is decided.The first day (DATEFIRST) the first day when set decided which your system uses one day to serve as a week.All and the following example is with serving as a week the first day to build on Sunday,Namely the first day of setting is 7.If the first day of your setting is different,You may need to adjust these example,Make it and conform to of different the first day of setting close.You can check the first day setting through @@DATEFIRST function.

To understand these example,We review DATEDIFF and DATEADD function first.DATEDIFF function is calculated the hour between two date, day, week, month, year wait for time-interval gross.DATEADD function is calculated a date is decreased through giving time-interval to add will obtain a new date.Should understand more DATEDIFF and DATEADD function and time-interval to be able to read Microsoft online help.

Use DATEDIFF and DATEADD function will calculate date,The consideration method with the date that changes you to need from current date originally is a bit different.You must from time-interval this respect considers.For instance,There is how many time-interval between the date that should get to you from current date,Or,Arrive from today some day (for instance 1900-1-1) between have how many time-interval,Etc.Understand the different date computation example that how time-interval of with a view to conduces to you understanding me easily.

The first day of a month

The first example,My general tells you how to go from current date the last day of this month.Notice please:The other case in this example and this article will use DATEDIFF and DATEADD function to calculate only the date that we mean.Each example will pass computation but the time-interval before,Undertake adding decreasing will get meaning calculative date next.

This is the SQL that calculates the first days month Script:
SELECT DATEADD(mm, DATEDIFF(mm, 0, getdate()) , 0)

We part this statement will see it is how to work.Most the function of core is Getdate() ,The function that major person knows to this is to return current date and time.The function DATEDIFF(mm that the next carries out, 0, getdate()) is computation current date and " 1900-01-01 00:00:00 " the month between this date is counted.Remember:It is like period and time variable and millisecond from " 1900-01-01 00:00:00 " begin calculative.This is why you are OK expression of the first time is appointed to be in DATEDIFF function " 0 " .Next function is DATEADD,Increase current date to " 1900-01-01 " the month is counted.The date that defines beforehand through increasing " 1900-01-01 " with the lunar number of current date,The first day when we can acquire this month.Additional,The time part of the date that computation comes out will be " 00:00:00 " .

This calculative skill is to be calculated first current date arrives " 1900-01-01 " time-interval is counted,Add it next " 1900-01-01 " come up obtain special date,This skill can use calculation a lot of different date.Next case also is to use this skill to produce different date from current date.


Of this week on Monday

I am to use here (the time-interval of Wk) will calculate one day which is this week on Monday.

SELECT DATEADD(wk, DATEDIFF(wk, 0, getdate()) , 0)

The first day a year

Use now year (the first day when the time-interval of Yy) will show this one year.

SELECT DATEADD(yy, DATEDIFF(yy, 0, getdate()) , 0)

The first day of the quarter

If you want calculation the first day of this quarter,This example tells you how to should be done.

SELECT DATEADD(qq, DATEDIFF(qq, 0, getdate()) , 0)

The midnight that day

Once needed to pass Getdate() function to be worth cut to drop time share for release time,Can consider current date is in in the middle of the night.If such,The time that this example use DATEDIFF and DATEADD function will come to obtain midnight is nodded.

SELECT DATEADD(dd, DATEDIFF(dd, 0, getdate()) , 0)

Thorough DATEDIFF and DATEADD function are calculated

You can understand,Through using simple DATEDIFF and DATEADD function calculation,You can discover a lot of different may significant date.

At present all example till just are calculated merely present time and " 1900-01-01 " the time-interval amount between,Add it next " 1900-01-01 " computation will give date on time-interval.The amount that assumes you revise time-interval,Perhaps use different time-interval to transfer DATEADD function,Subtractive perhaps time-interval is not to increase,So the date that you can discover and differs more through these small adjustment.

There are four examples to use another DATEADD function to calculate here the last day will replace DATEADD function around respectively two time-interval.

The last day last month

This is a computation last month the example of the last day.It tells associate the last day of a month subtracts 3 millisecond will obtain on this example.One a little bit wants to remember,In Sql The time in Server is accurate to 3 millisecond.This is why the date that I need to subtract 3 millisecond will obtain me to want and time.

SELECT Dateadd(ms, - 3, DATEADD(mm, DATEDIFF(mm, 0, getdate()) , 0) )

The time part of the date that computation comes out included a Sql Last hour of a day when Server can record (" 23:59:59:997 " ) time.

The last day last year

The example above join,To want to get the last day last year,Your need subtracts in the first sky this year 3 millisecond.

SELECT Dateadd(ms, - 3, DATEADD(yy, DATEDIFF(yy, 0, getdate()) , 0) )

The last day this month

Now,To win the last day this month,I need to amend the statement that obtains the last day last month a little.Modification need gives the date before using DATEDIFF to be become quite and " 1900-01-01 " add on returned time-interval 1.Through increasing a month,The first day of next month of my plan cipher out,Subtract next 3 millisecond,The last day that gave this month with respect to computation so.This is computation this month the SQL script of the last day.

SELECT Dateadd(ms, - 3, DATEADD(mm, DATEDIFF(m, 0, getdate())+1, 0) )

The last day of the current year

You should master this practice now,This is script of the last day of computational the current year

SELECT Dateadd(ms, - 3, DATEADD(yy, DATEDIFF(yy, 0, getdate())+1, 0) )

The first of this month Monday

good,Now is the last example.I should be calculated here the first of this month Monday.This is calculative script.
Select DATEADD(wk, DATEDIFF(wk, 0, Dateadd(dd, 6-datepart(day, getdate()) , getdate())) , 0)

In this example,I was used " of this week on Monday " script,Made little revise.Modification part is an original scenario in " Getdate() " the part replaces computation the 6th day of this month,The first when replace current date make acquire this month computation is OKly in computation with the 6th day of this month Monday.

Summary

You give one a little bit when I hope these example can use DATEADD and date of DATEDIFF function computation in you inspire.Through using the mathematical method of the time-interval of this computation date,I discover to show removed useful calendar is valuable between two date.Notice,This is a kind of method that computation gives these date only.Should remember well,Still a lot of methods can get same computational result.If you have other methods,That is very pretty good,If you are done not have,I hope these example can give you a few inspire,Should use DATEADD and DATEDIFF function calculation when you when your program may want the date that uses.

---------------------------------------------------------------
Appendix,Other date processing technique

Second of time of 1) take out Declare @ Datetime Set @ = Getdate() - - '2003-7-1 10:00:00'
SELECT @ , DATEADD(day, DATEDIFF(day, 0, @) , 0)

2) indication week a few Select Datename(weekday, getdate() )

3) the day number that how acquires a certain months Declare @m Int Set @m=2 - - month Select Datediff(day, '2003-'+cast(@m As Varchar)+'-15' , '2003-'+cast(@m+1 As Varchar)+'-15' )
Additional,Obtain the day is counted this month Select Datediff(day, cast(month(GetDate() ) As Varchar)+'-'+cast(month(GetDate() ) As Varchar)+'-15' , cast(month(GetDate() ) As Varchar)+'-'+cast(month(GetDate())+1 As Varchar)+'-15' )
The script that perhaps makes maneuver calculates the last day this month,Use DAY function area next the last day
SELECT Day(dateadd(ms, - 3, DATEADD(mm, DATEDIFF(m, 0, getdate())+1, 0)) )

4) judge whether bissextile:
SELECT Case Day(dateadd(mm, 2, dateadd(ms, - 3, DATEADD(yy, DATEDIFF(yy, 0, getdate()) , 0))) ) When 28 Then ' of ' non-leap year Else ' bissextile ' End
Or Select Case Datediff(day, datename(year, getdate())+'-02-01' , dateadd(mm, 1, datename(year, getdate())+'-02-01') ) When 28 Then ' of ' non-leap year Else ' bissextile ' End

5) a quarter how many days Declare @m Tinyint, @time Smalldatetime Select @m=month(getdate() ) Select @m=case When @m Between 1 And 3 Then 1 When @m Between 4 And 6 Then 4 When @m Between 7 And 9 Then 7 Else 10 End Select @time=datename(year, getdate())+'-'+convert(varchar(10) , @m)+'-01' Select Datediff(day, @time, dateadd(mm, 3, @time) )

...

Create Table Common_info(Name Varchar(10) , Value Int)Insert Into Common_info Values('msg_count' , 100)
Your Sql should so write (with memory process) : Create Proc P_ins_alm_msg
@msg_count Int OutAsSelect @msg_count = Value From Common_info Where Name = 'msg_count'Update Common_info Set Value = @msg_count+ 1 Where Name = 'msg_count' And Value =

@msg_countIf @@rowcount = 0Return -1000Insert Into Table1 (field0)Values(@msg_count)Return 0Go

...

Role change, part crossing-over, and monitoring server is in the position

When come to a standstill of the database on the line (the likelihood is the work is defended inside the plan,Or it is the state outside anticipating) ,If return the database on server of aid having equipment to be able to be offerred,access,You may set your mind at one a little bit quite.The daily record with a good design is deferent system (trade the database log file conveys from main server equipment aid server) can give you such self-confident heart.Built-in at SQL Serve 2000 the program of tool of Enterprise Manager of enterprise board and development edition bears a daily record namely deferent function.

...

Common ground is told,Data-base is to store the database of the data that has certain character.Normally,We differentiate the user of system of life data warehouse it is four kinds,The controller of data-base architect, data-base, person that use programming reachs the person that use commonly.Among them the management that data-base controller is in charge of Zhang date and safeguard,The access of the person that decide all data-base are used is restricted.Safety administration can say data-base is the job with data-base the mainest controller.The company level data-base that SQL Server is Microsoft,It is a function the powerful, data-base that uses easily,Can do safe mechanism conformity with the date of Zhang of the person that use of Windows NT/2000 directly.

So,After all what is safety administration?In short,Safety administration is to show the staff that joins a server to needing to ascend undertakes administrative.In using a program,We will be right of data-base of all kinds data of setting of the person that use operates attributive,It is the government that does Zhang date and password in using a program directly normally,But this kind of practice needs compose programme controll.And the figure that SQL Server has kind, easy operation uses an interface,Can manage the access of the person that use to SQL Server conveniently attributive.

...

The state that any database systems cannot avoid to break down,Although you used Clustered,Double machine heats up equipment... still cannot eradicate completely the only place breakdown in the system,What is more,the rather that to major user,Cannot bear so costly hardware invests.So,When the system breaks down,How to restore original valuable data to become an all in all issue.

When restore,The data file that optimal circumstance is you and log file are in good condition nondestructive,Need Sp_attach_db only so,Add data file to new database can,Perhaps be in stop machine when all data file (must Master waits) Copy falls to also go to original method,Do not recommend such way commonly nevertheless,Sp_attach_db is better,Although bother a lot of.

But,When general database breaks down, the system is may not can have time to wait for half-baked general affairs and dirty page those who write disk,Such circumstance Sp_attach_db can fail.So,Sent expectation to make a good disaster reinstate a plan at DBA.Plan according to your refreshment,Reductive and newest complete backup,Increment backs up or clerical log backs up,If if log of your mobile general affairs still can be read,coming out next,Congratulation you!You are OK and reductive the condition to the front of break down.

Common unit is the DBA that does not have full-time,If do not have practicable to back up,May be the time that backs up recently more too ages ago and cause not acceptability data loss,And log of your mobile general affairs also is in the position that cannot use,That is the most troubled condition.

Unfortunate is,General database breaks down is as a result of what memory subsystem causes,And such circumstance is almost impossible to practicable log is used at what restore.
So was forced to try these scheme.Of course,It is a requirement at least your data file is existence,If if data file, log file is mixed,backup was done not have,Do not look for me,You can go up to building top sing " god ah,Save save me " .

Above all,You can try Sp_attach_single_file_db,The data file that tries to restore you,Although can restore possibility is not large,If if this database just carried out a Checkpoint,passing,Succeed likely still.

If you had been done not have to the luck that has tombola bill,The most important database did not expect like you go on Attach in that way,Not crestfallen,Still have other program.

We can try to build a Log afresh,Install the database first for Emergency Mode,The Status of Sysdatabases states for 32768 the database is in this condition.

Nevertheless systematic watch is cannot change casually,Install first
Use Master
GoSp_configure 'allow Updates' , 1Reconfigure With Override
Go
NextUpdate Sysdatabases Set Status = 32768 Where Name = '%26lt;db_name%26gt;'
Now,Of Buddha of invocatory full deity bless,Build file of a Log afresh.Successful opportunity is quite great still,The system can approbate you to build the daily record that establish commonly.If did not report what is wrong,Can loosen now at a heat.

Although data was to restore,But do not think the thing calculates,finished,The work that having is missing for certain,Original data gets possibly also a few attaint.

Start SQL Server afresh first,The database that checks you next.
Set odd user pattern first,Do Dbcc nextSp_dboption '%26lt;db_name%26gt;' , 'single User' , 'true'
DBCC CHECKDB('%26lt;db_name%26gt;' )
If do not have what big question to be able to change database position,went back,The modification option that writes down so that did not forget to express the system is put out.Update Sysdatabases Set Status = 28 Where Name = '%26lt;db_name%26gt;'- - of course your database condition may not be this,The value with him appropriate instead.Also can use Sp_resetstatusGoSp_configure 'allow Updates' , 0Reconfigure With Override
Go
Checkdb when the report has the possibility a few mistakes,These erroneous data you may be forced to discard.Checkdb has option of a few kinds of rehabilitate,Oneself are looked at with,Nevertheless you may be returned finally is use REPAIR_ALLOW_DATA_LOSS,Complete all repair.Chekcdb can not complete all repair,We need further repair,To each with DBCC CHECKTABLE the watch makes an inspection.
The list of the watch can be used get inside Sysobjects,OBJECTPROPERTY be IsTable is all seek an inspection,Basically can solve a problem so,If return report error,Try to check data Select Into to another piece of watch.
After these finished,Wait for process of all index, view, memory, trigger to be built afresh.DBCC DBREINDEX perhaps can give you a few help.

...

The requirement of Director Bt is on duty management Ask as follows

1 a group of alignment N individual,Have 4 kinds of parts,Leader,Fellow,Aunt,Driver.The part presses N individual him basis to discharge fine line orderly

2 is on duty requirement:Week lead 1 times to weekday be on duty a driver is on duty;arrive weekday every night a fellow is on duty;Saturday weekday 2 aunts are on duty afternoon in the morning;Holiday leads a driver 1 times everyday a fellow in the morning afternoon 2 aunts

...

Create PROCEDURE Dbo.SP_SPIDtoIP @SPID Int
AS
- - SPID To MAC
- - Lj
DECLARE @MAC As Varchar(12)
SELECT @MAC = NET_ADDRESS FROM Master. .sysprocesses WHERE SPID = @SPID
- - MAC To IP
DECLARE @MACDisplay As Varchar(18)
DECLARE @IP As Varchar(15)
CREATE TABLE #temp (OUTPUT Varchar(255) Null)
SET NOCOUNT ON
INSERT INTO #temp EXEC Master. .xp_cmdshell 'arp -a'If @@error%26lt;%26gt;0Begin
RAISERROR ('The Level For Job_id:%D Should Be Between %d And %d. ' , 16, 1)
- - ROLLBACK TRANSACTIONEnd
SELECT @MACDisplay = LEFT(@MAC, 2) + '-' + SUBSTRING(@MAC, 3, 2) + '-' + SUBSTRING(@MAC, 5, 2) + '-' + SUBSTRING(@MAC, 7, 2) + '-' + SUBSTRING(@MAC, 9, 2) + '-' + SUBSTRING(@MAC, 11, 2) SELECT @IP = SUBSTRING(output, 3, 15) FROM #temp WHERE Output LIKE '%' + @MACDisplay + '%'
- - Resolve The IP
- - DECLARE @CMD As Varchar(100)
- - Select @CMD = 'master. .xp_cmdshell "ping -a ' + @IP + '"'
- - Exec (@CMD)
DROP TABLE #temp
SET NOCOUNT OFF
GO

...

In business system, different client has differring method to decimal processing

Pass the decimal digit that withholds in the setting, and decimal digit is the following the processing technique of mantissa, OK and agile contented client asks

Processing technique watch

...

People often can be immersed in an error when use SQL,Pay close attention to the result at earning too namely correct,And oversight the function difference that different implementation may exist between the method,Difference of this kind of function is in large or it is complex database environment in (if online general affairs handles DSS of OLTP or decision-making support system) in expression is gotten particularly apparent.The author discovers in working practice,Undesirable SQL is designed at impertinent index oneself toward come-and-go, deficient join condition and the Where clause that cannot optimize.After undertaking be optimizinged appropriately to them,Its traversal speed had apparent ground to rise!Below my general undertakes summing up respectively from these three respects:
To show an issue more intuitionisticly,The SQL run time in all example all passes a test,All express what do not exceed 1 second to be (%26lt;1 second) .
Test environment- -
Lead plane:HP LH II
Advocate frequency:330MHZ
Memory:128 million
Operating system:Operserver5.0.4
Database:Sybase11.0.3

One, unreasonable index design
Exemple:Watch Record has 620000 to go,Try look below different index,Below the moving circumstance of a few SQL:
1. Build on Date one is not a teeming index
...

When we are operating a database,Because the operation is undeserved,can cause database watch to be locked up to decide occasionally,So we often are at a loss,Do not know how to solve a lock to these watches,In Pl/sql Developer tool menu " Tools " inside " Sessions " can inquire the conversation that exists now,But we find that conversation very hard to was locked up to decide,It is more difficult to want to find the conversation that is locked up so,This makes inquire the statement can inquire a conversation that is locked up so below.As follows:

SELECT Sn.username, m.SID, sn.SERIAL# , m.TYPE,
DECODE (m.lmode,
0, 'None' ,
1, 'Null' ,
2, 'Row Share' ,
3, 'Row Excl. ' ,
4, 'Share' ,
5, 'S/Row Excl. ' ,
6, 'Exclusive' , Lmode, LTRIM (TO_CHAR (lmode, '990') )
) Lmode,
DECODE (m.request,
0, 'None' ,
1, 'Null' ,
2, 'Row Share' ,
3, 'Row Excl. ' ,
4, 'Share' ,
5, 'S/Row Excl. ' ,
6, 'Exclusive' , Request, LTRIM (TO_CHAR (m.request, '990') )
) Request, M.id1, m.id2
FROM V$session Sn, v$lock M
WHERE (sn.SID = M.SID AND M.request! = 0) - - existence locks up a request,Namely by block
OR ( Sn.SID = M.SID - - nonexistent lock requests,But the object that the lock decides is locked up to decide by other and conversational request
AND M.request = 0
AND Lmode! = 4
AND (id1, id2) IN (
SELECT S.id1, s.id2
FROM V$lock S
WHERE Request! = 0 AND S.id1 = M.id1
AND S.id2 = M.id2)
AND S.id2 = M.id2)
ORDER BY Id1, id2, m.request;

...