sql語句 關於日期時間、類型轉換的東西

標籤：style   io   strong   資料   ar   art   問題   div   

（一）

1, select update_date, CONVERT(VARCHAR(30),update_date,111) jj ,CONVERT(VARCHAR(30),update_date,8) aa from report_month   //update_date為datetime型，結果顯示為：

             update_date                                     jj                          aa

2007-01-06 16:14:50.437                     2007/01/06              16:14:50

select DATEPART(mm,sign_date) montha,DATEPART(dd,sign_date) daya,sign_date  from report_day

          montha       daya                             sign_date

            5                  1                     2009-05-01 00:00:00.000

SELECT DATENAME(month, getdate()) AS ‘Month Name‘

2.按日期段查詢：select * from report_day  where sign_date between ‘2009-5-1‘ And ‘2009-5-7‘（v)

           select * from report_day  where sign_date between ‘2009-4‘ And ‘2009-5‘（x)

3.　----日期函數----
　　DAY() --函數返回date_expression 中的日期值
　　MONTH() --函數返回date_expression 中的月份值
　　YEAR() --函數返回date_expression 中的年份值
　　DATEADD(<datepart> ,<number> ,<date>)
　　--函數返回指定日期date 加上指定的額外日期間隔number 產生的新日期
　　DATEDIFF(<datepart> ,<number> ,<date>)
　　--函數返回兩個指定日期在datepart 方面的不同之處
　　DATENAME(<datepart> , <date>) --函數以字串的形式返回日期的指定部分
　　DATEPART(<datepart> , <date>) --函數以整數值的形式返回日期的指定部分
　　GETDATE() --函數以DATETIME 的預設格式返回系統當前的日期和時間

       

----資料類型轉換函式----
　　CAST() 函數文法如下
　　CAST (<expression> AS <data_ type>[ length ])
　　CONVERT() 函數文法如下
　　CONVERT(<data_ type>[ length ], <expression> [, style])

select cast(shift_id as int)+1  aa from report_day

select  sum(convert(real,work_days)),week_days  aa from report_day  group by week_days

adoquery2.SQL.add(‘select  emp_id  工號,round(sum(cast(ot_hrs as real)),2) 平時加班,round(sum(cast(work_hrs as real)),2) 工作小時 from report_day  group by emp_id‘);

例如：select id,A.Name,A.Age ,
convert(int, sum(case 工資項目 when‘基本工資‘ then 工資金額 end ) )基本工資,
cast( sum(case 工資項目 when ‘補貼‘then 工資金額 end )As int) as 補貼,
convert(int,sum(case 工資項目 when ‘津貼‘then 工資金額 end)) as 津貼,
convert(int,sum (工資金額)) as 合計
from A,B where id=Emp_id group by id,A.Name,A.Age

4.昨天想到的按日期統計資料，後來在自己電腦上看以前當的資料時，找到解決辦法了，確實很實用。說白了，就是
只想得到日期或時間的一部分，而不是完整的日期和時間。然後在統計。

用到的資料庫和表（MS SqlServer資料庫）
use master
go

if exists(select * from sysdatabases where name=‘Spending‘)
drop database Spending

create database Spending
on
(
name=‘Spending_data‘,
filename=‘D:\Spending_data.mdf‘,
size=3 mb,
filegrowth=10%
)
log on
(
name=‘Spending_log‘,
filename=‘D:\Spending_log.ldf‘,
size=3 mb,
filegrowth=10%
)
go


use Spending
go

if exists(select * from sysobjects where name=‘consumption‘)
drop table consumption
go
create table consumption
(
  cid int primary key identity(1,1),
  cdate datetime not null,
  cmoney money not null
)
go


insert into consumption values(‘20090624‘,23)
insert into consumption values(‘20090625‘,56)
insert into consumption values(‘20090625‘,42)
insert into consumption values(‘20090626‘,45)
insert into consumption values(‘20090627‘,12)
insert into consumption values(‘20090628‘,32)
insert into consumption values(‘20090629‘,36)
insert into consumption values(‘20090701‘,16)

select * from consumption

比如我想統計每天的資料和（25號時候有兩條記錄）用下面語句，只統計6月份每一天的資料和（如果年份多，再加上限制條件）

select DATEPART(dd,cdate) as ‘號‘, sum(cmoney) from consumption  where datepart(mm,cdate)=6  group by DATEPART(dd,cdate)
顯示結果
24    23.00
25    98.00
26    45.00
、、、

統計一周的如下

select DATEPART(wk,cdate) as ‘周‘, sum(cmoney) from consumption group by DATEPART(wk,cdate)
顯示結果
26    178.00
27     84.00

統計月的如下

select datepart(mm,cdate) as ‘月份‘,sum(cmoney) from consumption where datepart(yy,cdate)=2009 group by datepart(mm,cdate)

顯示結果
6    246.00
7     16.00

以上用到的就是“DATEPART（）”函數。函數DATEPART（）的參數是兩個變數。第一個變數指定要抽取日期的哪一部分；第二個變數是實際的資料。

日期的各部分及其簡寫
日期部分           簡寫               值
year                yy                1753--9999
quarter             qq                1--4
month               mm                1--12
day of year         dy                1--366
day                 dd                1--31
week                wk                1--53
weekday             dw                1--7(Sunday--Saturday)
hour                hh                0--23
minute              mi                0--59
second              ss                0--59
milisecond          ms                0--999




再有就是我們資料庫某欄位設計為 datetime類型後，插入資料後，自動將毫秒數都顯示出來了
例如我上面插入的是 20090624，可是在查出來就變成2009-06-24 00:00:00.000，這樣在頁面
顯示肯定有問題，這種情況可以用CONVERT（）函數

CONVERT(data_type,expression,[style])

此樣式一般在時間類型(datetime,smalldatetime)與字串類型(nchar,nvarchar,char,varchar)
相互轉換的時候才用到.

語句及查詢結果：
SELECT CONVERT(varchar(100), cdate, 0):24 06 2009 21:57PM
SELECT CONVERT(varchar(100), cdate, 1): 06/24/09
SELECT CONVERT(varchar(100), cdate, 2): 09.06.24
SELECT CONVERT(varchar(100), cdate, 3): 24/06/09
SELECT CONVERT(varchar(100), cdate, 4): 24.06.09
SELECT CONVERT(varchar(100), cdate, 5): 24-06-09
SELECT CONVERT(varchar(100), cdate, 6): 24 06 09
SELECT CONVERT(varchar(100), cdate, 7): 06 24, 09
SELECT CONVERT(varchar(100), cdate,: 10:57:46
SELECT CONVERT(varchar(100), cdate, 9): 06 24 2009 21:57:46:827AM
SELECT CONVERT(varchar(100), cdate, 10): 06-24-09
SELECT CONVERT(varchar(100), cdate, 11): 09/06/24
SELECT CONVERT(varchar(100), cdate, 12): 090624
SELECT CONVERT(varchar(100), cdate, 13): 24 06 2009 21:57:46:937
SELECT CONVERT(varchar(100), cdate, 14): 10:57:46:967
SELECT CONVERT(varchar(100), cdate, 20): 2009-06-24 21:57:47
SELECT CONVERT(varchar(100), cdate, 21): 2009-06-24 21:57:47.157
SELECT CONVERT(varchar(100), cdate, 22): 06/24/09 21:57:47 PM
SELECT CONVERT(varchar(100), cdate, 23): 2009-06-24
SELECT CONVERT(varchar(100), cdate, 24): 10:57:47
SELECT CONVERT(varchar(100), cdate, 25): 2009-06-24 21:57:47.250
SELECT CONVERT(varchar(100), cdate, 100): 06 24 2009 21:57PM
SELECT CONVERT(varchar(100), cdate, 101): 06/24/2009
SELECT CONVERT(varchar(100), cdate, 102): 2009.06.24
SELECT CONVERT(varchar(100), cdate, 103): 24/06/2009
SELECT CONVERT(varchar(100), cdate, 104): 24.06.2009
SELECT CONVERT(varchar(100), cdate, 105): 24-06-2009
SELECT CONVERT(varchar(100), cdate, 106): 24 06 2009
SELECT CONVERT(varchar(100), cdate, 107): 06 24, 2009
SELECT CONVERT(varchar(100), cdate, 108): 10:57:49
SELECT CONVERT(varchar(100), cdate, 109): 06 24 2009 21:57:49:437PM
SELECT CONVERT(varchar(100), cdate, 110): 06-24-2009
SELECT CONVERT(varchar(100), cdate, 111): 2009/06/24
SELECT CONVERT(varchar(100), cdate, 112): 20090624
SELECT CONVERT(varchar(100), cdate, 113): 24 06 2009 21:57:49:513
SELECT CONVERT(varchar(100), cdate, 114): 10:57:49:547
SELECT CONVERT(varchar(100), cdate, 120): 2009-06-24 21:57:49

4,

1.顯示本月第一天

SELECT DATEADD(mm,DATEDIFF(mm,0,getdate()),0)
select convert(datetime,convert(varchar(8),getdate(),
120)+‘01‘,120)

2.顯示本月最後一天

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())+1,0))


3.上個月的最後一天

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

4.本月的第一個星期一i

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


5.本年的第一天

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

6.本年的最後一天

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

7.去年的最後一天

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


8.本季度的第一天

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


9.本周的星期一

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


10.查詢本月的記錄

select * from tableName where DATEPART(mm, theDate)
=DATEPART(mm, GETDATE()) and DATEPART(yy, theDate)
= DATEPART(yy, GETDATE())

11.查詢本周的記錄

select * from tableName where DATEPART(wk, theDate) = DATEPART
(wk, GETDATE()) and DATEPART(yy, theDate) = DATEPART(yy, GETDATE())

12查詢本季的記錄 註：其中:GETDATE()是獲得系統時間的函數。

select * from tableName where DATEPART(qq, theDate) = DATEPART
(qq, GETDATE()) and DATEPART(yy, theDate) = DATEPART(yy, GETDATE())

13.擷取當月總天數:

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.擷取當前為星期幾

DATENAME(weekday, getdate())