首页 新闻 赞助 找找看

SQL数据库中怎样将时间格式转换只得到年和月?其他都不要。

0
悬赏园豆:5 [已解决问题] 解决于 2016-05-28 08:48

就比如这个2015-11-24 08:46:00.000

我只想得到2015-11,应该怎么做啊?

人人如画的主页 人人如画 | 初学一级 | 园豆:140
提问于:2016-05-25 12:43
< >
分享
最佳答案
0

给你一个function 建立之后 想得到啥就得到啥

获取的语句  select FormatDate(getdate(),'YYYY-MM')

ALTER FUNCTION [dbo].[FormatDate]
(@date as datetime,
@formatstring as varchar(100)
)
RETURNS varchar(100) AS  
BEGIN 
    declare @datestring as varchar(100)

    set @datestring=@formatstring

    --year
    set @datestring=replace(@datestring, 'yyyy', cast(year(@date) as char(4)))
    set @datestring=replace(@datestring, 'yy', right(cast(year(@date) as char(4)),2))

    --millisecond
    set @datestring=replace(@datestring, 'ms', replicate('0',3-len(cast(datepart(ms,@date) as varchar(3)))) + cast(datepart(ms, @date) as varchar(3)))

    --month
    set @datestring=replace(@datestring, 'mm', replicate('0',2-len(cast(month(@date) as varchar(2)))) + cast(month(@date) as varchar(2)))
    set @datestring=replace(@datestring, 'm', cast(month(@date) as varchar(2)))

    --day
    set @datestring=replace(@datestring, 'dd', replicate('0',2-len(cast(day(@date) as varchar(2)))) + cast(day(@date) as varchar(2)))
    set @datestring=replace(@datestring, 'd',  cast(day(@date) as varchar(2)))

    --hour
    set @datestring=replace(@datestring, 'hh', replicate('0',2-len(cast(datepart(hh,@date) as varchar(2)))) + cast(datepart(hh, @date) as varchar(2)))
    set @datestring=replace(@datestring, 'h',  cast(datepart(hh, @date) as varchar(2)))
    
    --minute
    set @datestring=replace(@datestring, 'nn', replicate('0',2-len(cast(datepart(n,@date) as varchar(2)))) + cast(datepart(n, @date) as varchar(2)))
    set @datestring=replace(@datestring, 'n', cast(datepart(n, @date) as varchar(2)))

    --second
    set @datestring=replace(@datestring, 'ss', replicate('0',2-len(cast(datepart(ss,@date) as varchar(2)))) + cast(datepart(ss, @date) as varchar(2)))
    set @datestring=replace(@datestring, 's', cast(datepart(ss, @date) as varchar(2)))

    return @datestring
END

 

收获园豆:5
二洋 | 菜鸟二级 |园豆:267 | 2016-05-26 13:48
其他回答(2)
0

CAST(DATEPART(YEAR,getdate()) as varchar(4)) +'-'+right('00'+cast(DATEPART(MONTH,getdate()) as varchar(2)),2)

月份还给你补0

czd890 | 园豆:14292 (专家六级) | 2016-05-25 12:53
0

存储时间的时候最好存全的

查询显示的时候可以把时间当做字符串处理 取前7位:2016-11。

小刺猬001 | 园豆:660 (小虾三级) | 2016-05-25 16:08
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册