SQL Server Convert DateTime format conversion

Change the [datetime] field value of the table table in sqlserver from '2011-11-07 16:41:35.033' to '2011-11-07 00:00:00' and remove the hours, minutes and seconds. The [datetime] field must be datetime Type. UPDATE table SET [datetime]= Convert(char(11),[datetime],120)

　Get the current date and use convert to convert it into the datetime format we need.

　select CONVERT(varchar(12), getdate(), 112 )

　20040912

　--------------------------------------------- --------------------------

　select CONVERT(varchar(12), getdate(), 102)

　2004.09.12

　------ -------------------------------------------------- ----

　select CONVERT(varchar(12), getdate(), 101)

　09/12/2004

　--------------------- ---------------------------------------

　select CONVERT(varchar(12) , getdate (), 103 )

　　12/09/2004

　　-------------------------------- --------------------------

　select CONVERT(varchar(12) , getdate(), 104 )

　12.09.2004

　 --- -------------------------------------------------- -------

　select CONVERT(varchar(12) , getdate(), 105 )

　12-09-2004

　---------------- ------------------------------------------

　select CONVERT(varchar(12 ), getdate(), 106 )

　12 09 2004

　 ---------------------------------- --------------------------

　select CONVERT(varchar(12) , getdate(), 107 )

　09 12, 2004

　 - -------------------------------------------------- ----------

　select CONVERT(varchar(12) , getdate(), 108 )

　11:06:08

　---------------- --------------------------------------------------

　select CONVERT(varchar (12) , getdate(), 109 )

　09 12 2004 1

　-------------------------------- ----------------------------

　select CONVERT(varchar(12), getdate(), 110)

　09-12- 2004

　------------------------------------------------ -------------

　select CONVERT(varchar(12) , getdate(), 113 )

　12 09 2004 1

　------------- --------------------------------------------------

　select CONVERT(varchar(12) , getdate(), 114 )

　11:06:08.177

　-------------------------- --------------------------------

　declare @dateTime DateTime--Define a datetime variable

　set @dateTime =getdate(); --Get the current time of the system and assign it to the @dateTime field

　 --Short date format: yyyy-m-d

　 SELECT REPLACE(CONVERT(varchar(10),@dateTime,120),N'- 0','-')

　--Long date format: yyyy year mm month dd day

SELECT STUFF(STUFF(CONVERT(char(8),@dateTime,112),5,0,N'year') ,8,0,N'month')+N'day'

　--Long date format: yyyy year m month d day

　 SELECT DATENAME(Year,@dateTime)+N'year'+CAST(DATEPART(Month ,@dateTime) AS varchar)+N'month'+DATENAME(Day,@dateTime)+N'日'

　 --Complete date + time format: yyyy-mm-dd hh:mi:ss:mmm

SELECT CONVERT(char(11),@dateTime,120)+CONVERT(char(12),@dateTime,114)

　-------------------------- -------------------------------------------------- -----------------------

　2. Date calculation processing

　DECLARE @dt datetime

　SET @dt=GETDATE()

　DECLARE @number int

　　SET @number=3

　　--1. Specify the first day or last day of the year

　　--A. The first day of the year

　 SELECT CONVERT(char(5),@dt,120 ; One day or the last day

　--A. The first day of the quarter

　 SELECT CONVERT(datetime,CONVERT(char(8),DATEADD(Month,DATEPART(Quarter,@dt)*3-Month(@dt)- 2,@dt),120)+'1')

　　--B. The last day of the quarter (CASE judgment method)

　 SELECT CONVERT(datetime,CONVERT(char(8),DATEADD(Month,DATEPART(Quarter, @dt)*3-Month(@dt),@dt),120)

　+CASE WHEN DATEPART(Quarter,@dt) in(1,4)

　THEN '31'ELSE '30' END)

--C. The last day of the quarter (direct calculation method)

SELECT DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1+DATEPART(Quarter,@dt)*3-Month(@dt) ),@dt),120)+'1')

　--3. The first or last day of the month where the specified date is located

　--A. The first day of the month

　 SELECT CONVERT(datetime,CONVERT( char(8),@dt,120)+'1')

　　 --B. The last day of the month

　 SELECT DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1,@dt ),120)+'1')

　--C. The last day of the month (easy to use wrong method)

　 SELECT DATEADD(Month,1,DATEADD(Day,-DAY(@dt),@dt))

　　 --4. Any day of the week where the specified date falls

　 SELECT DATEADD(Day,@number-DATEPART(Weekday,@dt),@dt)

　　 --5. Any day of the week where the specified date falls

　--A. Sunday is the first day of the week

　SELECT DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-1)%7,@dt)

　--B. Monday is the first day of the week

SELECT DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-2)%7-1,@dt)