SQL Server time format
Last Update:2018-12-08
Source: Internet
Author: User
The specific website is not saved, but I am used to it. Code Remember to save the method and paste it now. I hope the original author will be considerate.
Copy code The Code is as follows: --- the first day of the first month of last month
Select convert (varchar (12), dateadd (mm, datediff (mm, 0, dateadd (mm,-1, getdate (), 0), 112)
--- Last day of last month
Select convert (varchar (12), dateadd (MS,-3, dateadd (mm, datediff (M, 0, getdate (), 0), 112)
-- 1. display the first day of the month
Select dateadd (mm, datediff (mm, 0, getdate (), 0)
Select convert (datetime, convert (varchar (8), getdate (), 120) + '01', 120)
-- 2. display the last day of the month
Select dateadd (day,-1, convert (datetime, convert (varchar (8), dateadd (month, 1, getdate (), 120) + '01', 120 ))
Select dateadd (MS,-3, dateadd (mm, datediff (M, 0, getdate () + ))
-- 3. Last day of last month
Select dateadd (MS,-3, dateadd (mm, datediff (mm, 0, getdate (), 0 ))
-- 4. the first Monday of the month
Select dateadd (wk, datediff (wk, 0, dateadd (DD, 6-datepart (day, getdate (), getdate (), 0)
-- 5. The first day of the year
Select dateadd (YY, datediff (YY, 0, getdate (), 0)
-- 6. The last day of the year
Select dateadd (MS,-3, dateadd (YY, datediff (YY, 0, getdate () + ))
-- 7. Last day of last year
Select dateadd (MS,-3, dateadd (YY, datediff (YY, 0, getdate (), 0 ))
-- 8. The first day of this quarter
Select dateadd (QQ, datediff (QQ, 0, getdate (), 0)
-- 9. Monday of the week
Select dateadd (wk, datediff (wk, 0, getdate (), 0)
-- 10. query records of this month
Select * From tablename where datepart (mm, thedate) = datepart (mm, getdate () and datepart (YY, thedate) = datepart (YY, getdate ())
-- 11. query the records of this week
Select * From tablename where datepart (wk, thedate) = datepart (wk, getdate () and datepart (YY, thedate) = datepart (YY, getdate ())
-- 12 query records of this season. Note: getdate () is a function used to obtain the system time.
Select * From tablename where datepart (QQ, thedate) = datepart (QQ, getdate () and datepart (YY, thedate) = datepart (YY, getdate ())
-- 13. Get the total number of days of the month:
Select datediff (DD, getdate (), dateadd (mm, 1, getdate ()))
Select datediff (day, dateadd (mm, datediff (mm, '', getdate (),''), dateadd (mm, datediff (mm ,'', getdate (), '2017-02-01 '))
-- 14. Obtain the current day of the week
Select datename (weekday, getdate ())
Select convert (varchar (12), getdate (), 101) -- 12/28/2007
Select convert (varchar (12), getdate (), 102) -- 2007.12.28
Select convert (varchar (12), getdate (), 103) -- 28/12/2007
Select convert (varchar (12), getdate (), 104) -- 28.12.2007
Select convert (varchar (12), getdate (), 105) -- 28-12-2007
Select convert (varchar (12), getdate (), 106) -- 28 12 2007
Select convert (varchar (12), getdate (), 107) -- 12 28,200 7
Select convert (varchar (12), getdate (), 108) -- 10:06:33
Select convert (varchar (12), getdate (), 109) -- 12 28 2007 1
Select convert (varchar (12), getdate (), 110) -- 12-28-2007
Select convert (varchar (12), getdate (), 111) -- 2007/12/28
Select convert (varchar (12), getdate (), 112) -- 20071228
Select convert (varchar (12), getdate (), 113) -- 28 12 2007 1
Select convert (varchar (12), getdate (), 114) -- 10: 07: 36: 623
Select convert (varchar, getdate (), 120) -- 2007-12-28 10:08:56
Select convert (varchar (10), getdate (), 120) --
Select Replace (replace (convert (varchar, getdate (), 120 ),'-',''),'',''),':','') -- 20071228101012