Common date acquisition in SQL

Read Catalogue

• Get Current Month Day
• Ways to get a variety of dates
• function parameters/Functions

Back to Top Get current Month day

Select MONTH (GETDATE ())     6               

Year () gets years

Day () Get date

Select Day (GETDATE ())

Use Datename () to obtain the day of the month

Select GETDATE () as ' current date ' select Datename (Year,getdate ()) as ' year ' Select Datename (Month,getdate ()) as ' month ' Select Datename ( Day,getdate ()) As ' Day ' select Datename (Dw,getdate ()) as ' Week ' Select Datename (Week,getdate ()) as ' Week number ' select Datename (Hour, GetDate ()) As ' time ' select Datename (Minute,getdate ()) as ' Sub ' Select Datename (Second,getdate ()) as ' seconds '

DatePart () Acquired date

Current date 2014-06-19

Select DatePart (Yy,getdate ())  2014select DatePart (Mm,getdate ())  6 Select DatePart (Dd,getdate ())  19

Get back to the top method for getting various dates

--1, first day of the month

SELECT DATEADD (Mm,datediff (Mm,0,getdate ()), 0)  

--2. Show last day of the month

Select DATEADD (Day,-1,convert (datetime,convert (varchar (8), DATEADD (Month,1,getdate ()), +) + ' ",") Select DATEADD (Ms,-3,dateadd (Mm,datediff (M,0,getdate ()) +1,0))  

--

--3. Last day of last month

--4. First Monday of the month

Select DATEADD (Wk,datediff (wk,0, DATEADD (Dd,6-datepart (Day,getdate ()), GETDATE ()), 0)

--5. First day of the year

--6. The last day of the year

SELECT DateAdd (Ms,-3,dateadd (Yy,datediff (Yy,0,getdate ()) +1,0)

--7. Last day of last year

SELECT DateAdd (Ms,-3,dateadd (Yy,datediff (Yy,0,getdate ()), 0))

--8. The first day of the quarter

SELECT DATEADD (Qq,datediff (Qq,0,getdate ()), 0)  

--9. Monday of the Week

--10. Querying this month's records

The condition can be written as:

--11. Querying this week's records

--12. Check the records for this season

--13. Get the total number of days in the month:

Select DATEDIFF (Dd,getdate (), DATEADD (mm, 1, GETDATE ())) Select DATEDIFF (Day,dateadd (mm, DATEDIFF (mm, ", GETDATE ())," ), DateAdd (mm, DateDiff (mm, ", GETDATE ()), ' 1900-02-01 ')

--14. Gets the current day of the week

Select Datename (Weekday, GETDATE ())

--15. DATEADD returns a new datetime value based on adding a period of time to the specified date

Example: Add 2 days to the date

Select DATEADD (day,2, ' 2014-10-15 ')--return: 2014-10-17 00:00:00.000

--16. DateDiff returns the number of date and time boundaries across two specified dates.

Select DateDiff (Day, ' 2004-09-01 ', ' 2004-09-18 ')--return: 17

--17. DatePart returns an integer representing the specified date portion of the specified date.

Year, month, date is day, hour hour, divided into minute, seconds for second

--18. Datename returns a string representing the specified date part of a specified date

SELECT Datename (Weekday, ' 2004-10-15 ')--return: Friday

--19. Day (), month (), year ()--can be compared with datepart

Select Current date =convert (varchar), GETDATE (), 120), Current time =convert (varchar (8), GETDATE (), ()  Select Datename (DW, ' 2004-10-15 ')  Select this year how many weeks =datename (week, ' 2004-10-15 '), today is the week of =datename (weekday, ' 2004-10-15 ')

Back to top function parameters/functions

GetDate ()--Returns the current date and time of the system DateDiff (INTERVAL,DATE1,DATE2)--Returns the difference between Date2 and date1 two dates in the interval specified way date2-date1 DateAdd (interval,number,date)--in the way specified by interval, plus the date after number DatePart (interval,date)--Returns the integer value corresponding to the specified part of the date, interval Datename (interval,date)--Returns the string name corresponding to the specified part of the date, interval

If you think reading this blog gives you something to gain, you might want to click " recommend " in the lower right corner.

If you want to find my new blog more easily, click on " Follow me "

If you want to give me more encouragement, you may wish to click on the right, " enjoy your stay"

Blog is his own summary of the results of learning, learning to summarize the knowledge-"analysis of the problem-" to solve the problem.

Where the views/descriptions in the text are incorrect, please correct them.

Thank you for your reading, if you are interested in the content of my blog, please continue to follow my following blog, I am yxtic.

Common date acquisition in SQL