field conversions for time types in SQL Server

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

Select CONVERT (varchar), getdate (), ()

20040912
------------------------------------------------------------

Select CONVERT (varchar), getdate (), 102)

2004.09.12
------------------------------------------------------------

Select CONVERT (varchar), getdate (), 101)

09/12/2004
------------------------------------------------------------

Select CONVERT (varchar), getdate (), 103)

12/09/2004
------------------------------------------------------------

Select CONVERT (varchar), getdate (), 104)

12.09.2004
------------------------------------------------------------

Select CONVERT (varchar), getdate (), ()

12-09-2004
------------------------------------------------------------

Select CONVERT (varchar), getdate (), 106)

12 09 2004
------------------------------------------------------------

Select CONVERT (varchar), getdate (), 107)

09 12, 2004
------------------------------------------------------------

Select CONVERT (varchar), getdate (), 108)

11:06:08
------------------------------------------------------------

Select CONVERT (varchar), getdate (), 109)

09 12 2004 1
------------------------------------------------------------

Select CONVERT (varchar), getdate (), ()

09-12-2004
------------------------------------------------------------

Select CONVERT (varchar), getdate (), 113)

12 09 2004 1
------------------------------------------------------------

Select CONVERT (varchar), getdate (),

11:06:08.177
------------------------------------------------------------

Declare @dateTime datetime--to define a dateTime variable
Set @dateTime =getdate (); --Get the current time of the system and assign a value to the @datetime field

--Short Date format: yyyy-m-d

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

--Long date format: yyyy mm month DD Day

SELECTSTUFF (CONVERT (char (8),@dateTime, STUFF),5,0,n' years '),8, 0,n' month ' )+n' day '

--Long date format: YYYY year M D Day

SELECTDatename (Year, @dateTime) +n year '

--Full Date + time format: Yyyy-mm-dd hh:mi:ss:mmm

SELECT Convert (char (one),@dateTime,+)+CONVERT (char (),@dateTime, )

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

2. Date Calculation processing

DECLARE @dt datetime
SET @dt=GETDATE ()
DECLARE @number int
SET @number=3

1. Specify a date the first or last day of the year
The first day of 1.1 years

SELECT CONVERT (char (5),@dt,+)+'1-1'

The last day of 1.2 years

SELECT CONVERT (char (5),@dt,+)+'12-31'

2. The first or last day of the quarter on which the specified date is located
First day of the 2.1 quarter

SELECTconvert (datetime, CONVERT (char (8), dateadd (month,datepart quarter, @dt) *3 -month ( @dt) -2,
@dt), 120) + 1

The last day of the 2.2 quarter (case judgment method)

SELECTCONVERT (DatetimeCONVERT (Char8),DATEADD (Month,DATEPART (Quarter,@dt)*3-month ( @dt), @dt), 120) + case when  DATEPART (Quarter, @dt)  in (1,then  '

The last day of the 2.3 quarter (direct reckoning method)

SELECTDATEADD (Day,-1,char (8), dateadd (month,1+datepart (Quarter, @dt) *3month ( @dt), @dt), Span style= "color: #800000; Font-weight:bold; " >120) + 1

3. The first or last day of the month in which the specified date is located
First day of 3.1 months

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

Last day of 3.2 months

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

Last day of 3.3 months (easy-to-use error method)

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

3.4 Any day of the week on which the specified date is located

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

5. Any day of the week on which the specified date is located
5.1 Days as the 1th day of the week

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

5.2 Weeks as the 1th day of the week

select dateadd ( @number -( @dt) +@ @DATEFIRST -%7-1, @dt)