Shulou(Shulou.com)06/01 Report--

The following are the common statements used in SQL date interception & SQL Server date comparison date query

Select CONVERT (varchar, getdate (), 120)

2004-09-12 11:06:08

Select replace (CONVERT (varchar, getdate (), 120),'-','),','),':',')

20040912110608

Select COUNVRT (varchar (12), getdate (), 23)

2008-04-01

Select CONVERT (varchar (12), getdate (), 111)

2004-09-12

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

20040912

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

2004.09.12

Other less commonly used date format conversion methods:

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

09/12/2004

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

12/09/2004

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

12.09.2004

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

12-09-2004

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

12 09 2004

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

09 12, 2004

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

11:06:08

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

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

The date portion of the intercept date in SQL Server:

Print convert (varchar (10), getdate (), 120)

The time portion of the intercept date in SQL Server:

Print convert (varchar (8), getdate (), 108)

The "date + time" section of the intercept date in SQL Server:

Select CONVERT (varchar (16), getDate (), 120)

Intercept the date section of the date field in SQL Server:

Convert (varchar (10), getdate (), 20)

The time section of intercepting the date field in SQL Server:

Convert (varchar (10), getdate (), 8)

Here's how to use the DATEADD and DATEDIFF functions to figure out some of the different dates you might use in your program

This is the SQL script that calculates the first day of the month:

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

Monday of this week

Here I use the interval of the week (wk) to calculate which day is Monday of the week.

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

The first day of the year

Now use the interval of the year (yy) to show the first day of the year.

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

The first day of the quarter

If you want to calculate the first day of the quarter, this example tells you what to do.

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

In the middle of the night

You used to need to truncate the time part through the getdate () function to return the time value, taking into account whether the current date is in the middle of the night. If so, this example uses the DATEDIFF and DATEADD functions to get the midnight time.

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

The last day of last month

This is an example of calculating the last day of last month. It is obtained by subtracting 3 milliseconds from the last day of the month. One thing to keep in mind is that in Sql Server the time is accurate to 3 milliseconds. That's why I need to subtract 3 milliseconds to get the date and time I want.

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

The last day of last year

To connect to the above example, to get the last day of last year, you need to subtract 3 milliseconds from the first day of this year.

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

The last day of the month

Now, in order to get the last day of this month, I need to modify the statement to get the last day of last month slightly. The modification requires adding 1 to the current date compared with the time interval returned by "1900-01-01" with DATEDIFF. By adding one month, I calculate the first day of the next month, and then subtract 3 milliseconds, thus calculating the last day of the month. This is the SQL script that calculates the last day of the month.

SELECT dateadd (ms,-3,DATEADD (mm, DATEDIFF (mmem0je getdate ()) + 1,0)

The last day of the year

You should master this practice now. This is the last day script of the year.

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

The first Monday of this month

Here I'm going to calculate the first Monday of the month. This is a calculation script.

Select DATEADD (wk, DATEDIFF (wk,0, dateadd (dd,6-datepart (day,getdate ()), getdate ()), 0)

Appendix, other date processing methods

1) remove the time, minutes and seconds

Declare @ datetime

Set @ = getdate ()-- '2003-7-1 1000001'

SELECT @, DATEADD (day, DATEDIFF (day,0,@), 0)

2) show what day it is

Select datename (weekday,getdate ())

3) how to get the number of days in a month

Declare @ m int

Set @ massif February-month

Select datediff (day,'2003-'+cast (@ m as varchar) +'- 15', '2003-'+cast (@ masked 1 as varchar) +'-15')

In addition, get the number of days of the 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')

Or use a script that calculates the last day of the month, and then use the DAY function area for the last day

SELECT Day (dateadd (ms,-3,DATEADD (mm, DATEDIFF) + 1,0))

4) to judge whether it is a leap year:

SELECT case day (dateadd (mm, 2, dateadd (ms,-3,DATEADD (yy, DATEDIFF (yy,0,getdate (), 0) when 28 then 'normal year' else 'leap year' end

Or

Select case datediff (day,datename (year,getdate ()) +'- 02-01 date add (mm,1,datename (year,getdate ()) +'- 02-01'))

When 28 then 'normal year' else 'Leap year' end

5) how many days in a quarter

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))

Select * from pms_t_TMonPersonalPerformance

WHERE VisitDate between cast ('2015-01-01 23 and cast 22 as datetime) and cast (' 2015-03-01 22 15'as datetime)

Convert conversion

Select * from pms_t_TMonPersonalPerformance WHERE VisitDate between CONVERT (datetime,'2014-01-01') and CONVERT (datetime,'2014-03-01')

Take milliseconds

Select * from checkinout where userid='56409' and substring (convert (char (24), checktime,121), 22) '00'

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.