DATEPART()
函数用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等等。

一、MySQL查询今天、昨天、上周、近30天、去年等的数据的方法：

DECLARE @Date  DATETIME
SET @Date=GETDATE()
–前一天，给定日期的前一天
SELECT DATEADD(DAY,-1,@Date) AS ‘前一天’
–后一天，给定日期的后一天
SELECT DATEADD(DAY,1,@Date) AS ‘后一天’
GO

Formatting Dates

Examples of calculating and formatting dates

*/

–To get the full Weekday name

SELECT DATENAME(dw,GETDATE())

–To get the abbreviated Weekday name (MON,
TUE, WED etc)

SELECT LEFT(DATENAME(dw,GETDATE()),3)

–ISO-8601 Weekday number

SELECT DATEPART(dw,GETDATE())+(((@@Datefirst+3)%7)-4)

–Day of the month with leading zeros

SELECT RIGHT(’00’

• CAST(DAY(GETDATE())
  AS VARCHAR),2)

–Day of the month without leading space

SELECT CAST(DAY(GETDATE())
AS VARCHAR)

–day of the year

SELECT DATEPART(dy,GETDATE())

–number of the week in the year

SELECT DATEPART(week,GETDATE())

–ISO-8601 number of the week of the year
(monday as the first day of the week)

SET datefirst 1 SELECT DATEPART(week,GETDATE())

–you may need to preserve and restore the
value

–full name of the month

SELECT DATENAME(MONTH,GETDATE())

–Abbreviated name of the month

SELECT LEFT(DATENAME(MONTH,GETDATE()),3)–not true
of finnish or french!

–Number of the month with leading zeros

SELECT RIGHT(’00’

• CAST(MONTH(GETDATE())
  AS VARCHAR),2)

–two-digit year

SELECT RIGHT(CAST(YEAR(GETDATE())
AS VARCHAR),2)

–four-digit year

SELECT CAST(YEAR(GETDATE())
AS VARCHAR)

–hour (00-23)

SELECT DATEPART(hour,GETDATE())

–Hour (01-12)

SELECT LEFT(RIGHT(CONVERT(CHAR(19),GETDATE(),100),7),2)

–minute

SELECT DATEPART(minute,GETDATE())

–second

SELECT DATEPART(second,GETDATE())

–PM/AM indicator

SELECT RIGHT(CONVERT(CHAR(19),GETDATE(),100),2)

–time in 24 hour notation

SELECT CONVERT(VARCHAR(8),GETDATE(),8)

–Time in 12 hour notation

SELECT RIGHT(CONVERT(CHAR(19),GETDATE(),100),7)

–timezone (or daylight-saving)

SELECT DATEDIFF(hour,
GETDATE(), GETUTCDATE())

—-ordinal suffix for the date

SELECT SUBSTRING(‘stndrdthththththththththththththththththstndrdthththththththst’

,(DATEPART(DAY,GETDATE())*2)-1,2)

–full date (the variations are infinite.
Here is one example

SELECT DATENAME(dw,GETDATE())+’,
‘+ STUFF(CONVERT(CHAR(11),GETDATE(),106),3,0,

SUBSTRING(‘stndrdthththththththththththththththththstndrdthththththththst’

,(DATEPART(DAY,GETDATE())*2)-1,2))

–e.g. Thursday, 12th Oct 2006/*

 

DATEDIFF() 函数返回两个日期之间的时间差。

 

–月初，计算给定日期所在月的第一天
–这个计算的技巧是先计算当前日期到”1900-01-01″的时间间隔数，然后把它加到”1900-01-01″上来获得特殊的日期
–这个技巧可以用来计算很多不同的日期
DECLARE @Date  DATETIME
SET @Date=GETDATE()
SELECT DATEADD(MONTH,DATEDIFF(MONTH,’1900-01-01′,@Date),’1900-01-01′) AS ‘所在月的第一天’
–精简算法，根据SQL
Server的时间表示方式可知，’1900-01-01′ 可以用0代替
SELECT DATEADD(MONTH,DATEDIFF(MONTH,0,@Date),0) AS ‘所在月的第一天’
–上面两种算法精确到天时分秒均为00:00:00.000
–下面算法课以保留时分秒
–思路：用给定日期减去月第一天与给定日期差的天数
SELECT DATEADD(DAY,1-DATEPART(DAY,@Date),@Date)
GO

Calculating Dates by example

*/

— now

SELECT GETDATE()

— Start of today (first thing)

SELECT CAST(CONVERT(CHAR(11),GETDATE(),113)
AS datetime)

–or …

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

–or …

SELECT cast(cast
(GETDATE()
as date) as datetime)

— Start of tomorrow (first thing)

SELECT CAST(CONVERT(CHAR(11),DATEADD(DAY,1,GETDATE()),113)
AS datetime)

— Start of yesterday (first thing)

SELECT CAST(CONVERT(CHAR(11),DATEADD(DAY,-1,GETDATE()),113)
AS datetime)

— This time Next thursday (today if it
is thursday)

SELECT DATEADD(DAY,((7-DATEPART(dw,GETDATE())+(((@@Datefirst+3)%7)+2)) % 7),GETDATE())

— Two hours time

SELECT DATEADD(hour,2,GETDATE())

— Two hours ago

SELECT DATEADD(hour,-2,GETDATE())

— Same date and time last month

SELECT DATEADD(MONTH,-1,GETDATE())

— Start of the month

SELECT CAST(’01 ‘+ RIGHT(CONVERT(CHAR(11),GETDATE(),113),8) AS datetime)

— Start of last month

SELECT CAST(’01 ‘+ RIGHT(CONVERT(CHAR(11),DATEADD(MONTH,-1,GETDATE()),113),8) AS datetime)

— Start of next month

SELECT CAST(’01 ‘+ RIGHT(CONVERT(CHAR(11),DATEADD(MONTH,1,GETDATE()),113),8) AS datetime)

–last day this month

select dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate()  )+1, 0))

— Ten minutes ago

SELECT DATEADD(minute,-10,GETDATE())

— Midnight last night

SELECT CAST(CONVERT(CHAR(11),GETDATE(),113)
AS datetime)

— Midnight tonight

SELECT CAST(CONVERT(CHAR(11),DATEADD(DAY,1,GETDATE()),113)
AS datetime)

— Three weeks ago

SELECT DATEADD(week,-3,GETDATE())

— Start of the week (this depends on
your @@DateFirst setting)

SELECT DATEADD(DAY, -(DATEPART(dw,GETDATE())-1),GETDATE())

–first day of the current quarter

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

— last year

SELECT DATEADD(YEAR,-1,GETDATE())

–final day of previous year

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

— new year, this year

SELECT CAST(’01
Jan’+ DATENAME(YEAR,GETDATE()) AS datetime)

–or

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

— new year, last year

SELECT CAST(’01
Jan’+ DATENAME(YEAR,DATEADD(YEAR,-1,GETDATE())) AS
datetime)

— Last moment of this year

select dateadd(ms,-1,DATEADD(yy, DATEDIFF(yy,0,getdate()  )+1, 0))

— next christmas

SELECT CASE WHEN
DATEPART(dy,GETDATE())<DATEPART(dy,’25 Dec’+

• DATENAME(YEAR,GETDATE()))

THEN CAST(’25
Dec’+ + DATENAME(YEAR,GETDATE()) AS datetime)

ELSE
CAST(’25
Dec’+ CAST(DATEPART(YEAR,GETDATE())+1 AS
VARCHAR) AS datetime)
END

 /*

 

--查找当天上午的数据 select * from R_Attendance where (datepart(hour,RDatetime)=0 and datepart(hour,RDatetime)12) and datediff(d,RDatetime,GetDate())=0 --查找当天下午的数据 select * from R_Attendance where (datepart(hour,RDatetime)12 and datepart(hour,RDatetime)=23) and datediff(d,RDatetime,GetDate())=0 

–月末，计算给定日期所在月的最后一天
DECLARE @Date  DATETIME
SET @Date=GETDATE()
–思路：当前月的下一月1号在减1天
SELECT DATEADD(DAY,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,’1900-01-01′,@Date),’1900-01-01′)) AS ‘所在月的最一天’
SELECT DATEADD(MONTH,1+DATEDIFF(MONTH,’1900-01-01′,@Date),’1900-01-01′)-1
AS ‘所在月的最一天’
–1900-01-01 用0代替
SELECT DATEADD(DAY,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,0,@Date),0)) AS ‘所在月的最一天’
SELECT DATEADD(MONTH,1+DATEDIFF(MONTH,0,@Date),0)-1
AS ‘所在月的最一天’
–思路：与月初计算思路相同
SELECT DATEADD(MONTH,DATEDIFF(MONTH,’1989-12-31′,@Date),’1989-12-31′) AS ‘所在月的最一天’
–精简算法，’1989-12-31′ 用-1代替
SELECT DATEADD(MONTH,DATEDIFF(MONTH,-1,@Date),-1)
AS ‘所在月的最一天’
–保留时分秒的算法
SELECT DATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date)))
GO

Date Conversions

When converting from SQL Server dates to Unix timestamps, the dates are
rounded to the nearest second (Unix timestamps are only accurate to the
nearest second) SQL Server date to UNIX timestamp (based on seconds
since standard epoch of 1/1/1970)

*/

 

SELECT DATEDIFF(second,’1/1/1970′,GETDATE())
— UNIX timestamp to SQL Server

SELECT DATEADD(second,
1160986544, ‘1/1/1970’)

 

/* The newer datatypes can give some fascinating information. Here is
an instant way of finding what the current time and date is, in a
variety of parts of the world.*/

 

DECLARE
@Timezones TABLE( timezone CHAR(6), Place VARCHAR(30))

INSERT INTO @Timezones (timezone,
Place)

 VALUES

   (‘-10:00’, ‘Hawaii’),

   (‘-09:00’, ‘Alaska’),

   (‘-08:00’, ‘Los Angeles’),

   (‘-07:00’, ‘Arizona’),

   (‘-06:00’, ‘Chicago’),

   (‘-05:00’, ‘New York’),

   (‘-03:00’, ‘Rio De Janeiro’),

   (‘-01:00’, ‘Azores’),

   (‘-00:00’, ‘London’),

   (‘+01:00’, ‘Berlin’),

   (‘+02:00’, ‘Cairo’),

   (‘+03:00’, ‘Moscow’),

   (‘+04:00’, ‘Dubai’),

   (‘+05:00’, ‘Islamabad’),

   (‘+05:30’, ‘Bombay’),

   (‘+07:00’, ‘Bangkok’),

   (‘+08:00’, ‘Beijing’),

   (‘+09:00’, ‘Tokyo’),

   (‘+10:00’, ‘Sydney’),

   (‘+12:00’, ‘Auckland’)

SELECT

 
Place, CONVERT(CHAR(20), SWITCHOFFSET(SYSDATETIMEOFFSET( ),
timezone), 113)

FROM
@timezones ORDER BY  place

/* We’ve put a fuller version of this in the speechbubble at the top of
the article.

计算两个时间差

删除建立时间超过3天的订单记录

 

delete 订单表 where datediff( dd, order_addtime, getdate() ) > 3
用函数datediff() datediff( dd, 时间1, 时间2
)，意思是：计算时间1到时间2之间的天数 所以，datediff( dd,
order_addtime, getdate() ) > 3，就是超过3天的

今天  

select * from 表名 where to_days(时间字段名) = to_days(now());  
昨天  
SELECT * FROM 表名 WHERETO_DAYS(NOW( ) ) – TO_DAYS(
时间字段名) <= 1  
7天  
SELECT * FROM 表名 whereDATE_SUB(CURDATE(), INTERVAL 7 DAY)
<=date(时间字段名)  
近30天  
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY)
<=date(时间字段名)  
本月  
SELECT * FROM 表名 WHEREDATE_FORMAT( 时间字段名, ‘%Y%m’ )
=DATE_FORMAT( CURDATE( ) , ‘%Y%m’ )  
上一月  
SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , ‘%Y%m’ )
, date_format( 时间字段名, ‘%Y%m’ ) ) =1  
#查询本季度数据  
select * from `ht_invoice_information`
whereQUARTER(create_date)=QUARTER(now());  
#查询上季度数据  
select * from `ht_invoice_information` where
QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));  
#查询本年数据  
select * from `ht_invoice_information` where
YEAR(create_date)=YEAR(NOW());  
#查询上年数据  
select * from `ht_invoice_information` where
year(create_date)=year(date_sub(now(),interval 1 year));  
查询当前这周的数据   
SELECT name,submittime FROM enterprise WHERE
YEARWEEK(date_format(submittime,’%Y-%m-%d’)) = YEARWEEK(now());  
查询上周的数据  
SELECT name,submittime FROM enterprise
WHEREYEARWEEK(date_format(submittime,’%Y-%m-%d’))
=YEARWEEK(now())-1;  
查询当前月份的数据  
select name,submittime from enterprise   where
date_format(submittime,’%Y-%m’)=date_format(now(),’%Y-%m’)  
查询距离当前现在6个月的数据  
select name,submittime from enterprise where submittime between
date_sub(now(),interval 6 month) and now();  
查询上个月的数据  
select name,submittime from enterprise   where
date_format(submittime,’%Y-%m’)=date_format(DATE_SUB(curdate(),
INTERVAL 1 MONTH),’%Y-%m’)  
select * from ` user ` where DATE_FORMAT(pudate, ‘ %Y%m ‘ ) =
DATE_FORMAT(CURDATE(), ‘ %Y%m ‘ ) ;  
select * from user where WEEKOFYEAR(FROM_UNIXTIME(pudate,’%y-%m-%d’))
= WEEKOFYEAR(now())  
select *   
from user
where MONTH (FROM_UNIXTIME(pudate, ‘ %y-%m-%d ‘ )) = MONTH (now())  
select *   
from [ user ]   
where YEAR (FROM_UNIXTIME(pudate, ‘ %y-%m-%d ‘ )) = YEAR (now())  
and MONTH (FROM_UNIXTIME(pudate, ‘ %y-%m-%d ‘ )) = MONTH (now())  
select *   
from [ user ]   
where pudate between 上月最后一天  
and 下月第一天  
where date(regdate)   =   curdate();  
select   *   from   test   where year(regdate)=year(now())   and
month(regdate)=month(now())   and day(regdate)=day(now())  
SELECT date( c_instime ) ,curdate( )  
FROM `t_score`  
WHERE 1  
LIMIT 0 , 30 

二、相关函数简介

 

–其他月计算
–计算给定日期所在月的上月第一天
DECLARE @Date  DATETIME
SET @Date=GETDATE()
–当前月第一天减去一个月
SELECT DATEADD(MONTH,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date),0)) AS ‘上月第一天’
–简化
SELECT DATEADD(MONTH,DATEDIFF(MONTH,0,@Date)-1,0)
AS ‘上月第一天’
–另一种当前月第一天算法
SELECT DATEADD(MONTH,-1,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date)) ‘上月第一天’
GO

Using dates

When storing dates, always use one of the date/time data types. Do not
feel tempted to use tricks such as storing the year, month or day as
integers, with the idea that this

will help retrieval and aggregation for reports. It never does.

if you use the DATETIMEOFFSET, you are reasonably future-proof as you
store dates as the UTC date together with the offset. This means that
you can do dime-and-date calculations on data, even if it has been taken
from more than one time zone.

The manipulation of the date/time data
types is so critical to SQL Server’s performance that it is
highly optimised. indexes based on date/time data type work very well, sort
properly, and allow fast partitioning on a variety of criteria such as
week, month, year-to-date and so on.

If, for example, you store a list of purchases by date in a table such
as PURCHASES

you can find the sum for the previous week by… */

 

SELECT SUM(total)
FROM purchases

WHERE purchaseDate BETWEEN DATEADD(week,-1,GETDATE())
AND GETDATE()

–this will pick up an index on PurchaseDate

–what about sales since the start of the week

 

SELECT SUM(total)
FROM purchases

WHERE purchaseDate BETWEEN

DATEADD(DAY,
-(DATEPART(dw,GETDATE())-1),GETDATE())
AND GETDATE() –Want a
daily total?

SELECT CONVERT(CHAR(11),PurchaseDate,113),

SUM(total)
FROM purchases

GROUP BY CONVERT(CHAR(11),PurchaseDate,113)

ORDER BY MIN(PurchaseDate)

 

–Or to find out which days of the week were the best?

SELECT DATENAME(dw,PurchaseDate),

[No. Purchases]=COUNT(*),
[revenue]=SUM(total)
FROM [purchases]

GROUP BY DATENAME(dw,PurchaseDate), DATEPART(dw,PurchaseDate)

ORDER BY DATEPART(dw,PurchaseDate)

–Want a week by week total?

SELECT ‘Week ‘+DATENAME(week,purchaseDate)+’
‘+DATENAME(YEAR,purchaseDate),

SUM(total)
FROM purchases

GROUP BY ‘Week ‘+DATENAME(week,purchaseDate)+’
‘+DATENAME(YEAR,purchaseDate)

ORDER BY MIN(InsertionDate)

 

–(you’d miss weeks where nothing was
purchased if you did it this way.)

/* The LIKE expression can be used for
searching for datetime values.

If, for example, one wants to search for all purchases done at 9:40, one
can find

a match by the clause WHERE purchaseDate LIKE ‘%9:40%’. */

SELECT * FROM
[purchases]

WHERE purchaseDate LIKE ‘%9:40%’

 

–or all purchases in the month of february

SELECT COUNT(*)
FROM [purchases]

WHERE purchaseDate LIKE ‘%feb%’

 

–all purchases where there is a ‘Y’ in the
month (matches only May!)

SELECT DATENAME(MONTH,
insertionDate), COUNT(*)
FROM [purchases]

WHERE purchaseDate LIKE ‘%y%’

GROUP BY DATENAME(MONTH,
purchaseDate)

/* this ‘Like’ trick is of limited use and should be used with
considerable caution as

it uses artifice to get its results*/