Some experiences on using the date type in SQL Server

I,Previously, I used to compare two datetime data types, A and B, to determine whether the same year and the same month are the same method:

Where   Year () =   Year (B) And   Month () =   Month (B) And   Day () =   Day (B)

Actually, it can be implemented in a more concise way.

Where   Datediff ( Day , A, B) =   0

Similarly, the method to compare whether A and B are in the same month of the same year is:

Where   Datediff ( Month , A, B) =   0

For more information about datediff, see books online.

II,In the date function of the ms SQL Server, "0" can be used to replace the date, "0" indicates the date is.

III,Removes the time, minute, and second information returned by the getdate () function.

Dateadd ( Day , Datediff ( Day , 0 , Getdate ()), 0 )

IV,Display the time returned by the getdate () function

Select   Datename (Weekday, Getdate ())

The result is related to the value of @ datefirst.

V,The default datetime format of the Chinese version of SQL Server is yyyy-mm-dd thh: mm: Ss. Mmm. If we need different date formats such as: 2005/10/27, 2005-10-27,
You can use the following function:

Select   Convert ( Varchar ( 10 ), Time, 120 )

here, 120 can be replaced by a value between 101 and 114 (108 obtains the time ). Of course, you must change varchar (10) To varchar (12) in several cases to display it completely.
below are several common values:
120 result: 2005-10-27
102 result: 2005.10.25
111 result: 2005/10/27
101 result: 10/27/2005
112 results: 20051027