In SQL, how does one add year, month, day, hour, minute, and second to datetime data?

When you troubleshoot SharePoint problems, you often need to query the SharePoint database. Sometimes you need to perform some operations on the time.

For example, when troubleshooting alert problems, you often need to use the following query:

 
SelectItemname,Eventdata,ACL,EventtimeFromEventcacheWhere(ACLIs not null) and (EventdataIs not null)OrderEventtimeDesc
 

 

However, the eventtime here is the GMT time, which does not seem intuitive to us in China, because we are using GMT + 8 Beijing time. You can use the following statement to correct the result.

SelectItemname,Eventdata,ACL,Dateadd(Hour,8,Eventtime)FromEventcacheWhere(ACLIs not null) and (EventdataIs not null)OrderEventtimeDesc
 

 

You can find the answer in the following documents to modify the year, month, or day.

Dateadd (TRANSACT-SQL)

http://msdn.microsoft.com/en-us/library/ms186819.aspx