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*/
相关文章
- 1.连锁概念,外接连(左外界连接
- 但在应用进程中,自从学习并利用它使用到实在的支出的进度中照旧会蒙受不菲的难点
- 澳门新葡8455最新网站Centos7使用yum安装MySQL甚至哪些落实长途连接,Linux平台上引入应用RPM包来安装Mysql
- · ‘.’相配任何单个的字符,注目的在于你接受SQL格局时
- name4为数据库字段卡塔尔 1.方法 复制代码 代码如下,局地变量是@开首
- 澳门新葡8455最新网站克罗地亚语字符只须求四个字节存款和储蓄就丰富了,VARCHARubicon类型的其实尺寸是它的值的实在尺寸+1
- 将表值表达式的列转变为列值,PIVOT运算符将输入表的行旋转为列
- 那将要跟数据库服务器交互作用1W次,必需必要连接的数字
- 澳门新葡8455最新网站DATEDIFF(卡塔尔(قطر 函数再次来到八个日子之间的光阴差
- 德姆oUser.然后创建客商并mapping到数据库,那么要怎么准确的去除这个Windows认证账号呢