带有 Datediff 和 DatePart 的 Case 语句 [英] Case Statement with Datediff and DatePart

查看:23
本文介绍了带有 Datediff 和 DatePart 的 Case 语句的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

有人可以帮忙写一个case语句吗,我需要的是要显示的查询如下.我知道有一些方法可以更轻松地做到这一点,但我只需要案例陈述方面的帮助.

Can some one help with a case statement please, what I need is the query to show is the following. I know there are ways to do this easier but I just need help on the Case Statement.

--如果当前月份小于"出生月份,那么从总年份中取1"给我 41.--如果当前月份大于"出生月份,则年龄是正确的.--但是,如果当前月份与 DOB 月份相等",那么我们需要转到天级别以获得正确的年龄.

--If the Current Month is ‘Less Than’ the DOB Month, then take ‘1’ of the Total Years to give me 41. --If the Current Month is ‘Greater Than’ the DOB Month then the Age is Correct. --However if the Current Month is ‘Equal’ to the DOB Month then we need to go to Day level to get the correct Age.

Set @DOB = '01 November 1971'
Set @Today = GETDATE()


SELECT Datediff(Year,@DOB,@Today) AS Years, 
Datepart(Month,@DOB) As DOB_Month, 
Datepart(Day, @DOB) as DOB_Day,
DatePart(Month, @Today) As Current_Month, 
Datepart(Day,@Today) AS Current_Day

推荐答案

试试这个:

DECLARE @DOB DATE= '01 November 1971'
DECLARE @TODAY DATE = GETDATE()

SELECT CASE 
WHEN DATEPART(MONTH, @TODAY) < DATEPART(MONTH,@DOB) THEN DATEDIFF(YEAR,@DOB,@TODAY) - 1
WHEN DATEPART(MONTH, @TODAY) > DATEPART(MONTH,@DOB) THEN DATEDIFF(YEAR,@DOB,@TODAY)
ELSE
    CASE 
         WHEN DATEPART(DAY, @TODAY) < DATEPART(DAY,@DOB) THEN DATEDIFF(YEAR,@DOB,@TODAY) - 1
         ELSE DATEDIFF(YEAR,@DOB,@TODAY)  
    END       
END

这篇关于带有 Datediff 和 DatePart 的 Case 语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆