当输入为 24 小时或 48 小时时，如何不查看小时值的 0

Question

DECLARE @Hours AS INT
DECLARE @SubtractDate AS DATETIME
SET @Hours=24 /* User input to have hours */
SET @SubtractDate=DATEADD(hh,@Hours,GETDATE()) - GETDATE()
if @Hours>=24 

SELECT CONVERT(VARCHAR(10),DATEDIFF(DAY,'1900-01-01',@SubtractDate))+ ' Day(s) ' +
CONVERT(VARCHAR(10),DATEPART(hh,@SubtractDate))+ ' Hour(s) 'AS [Result]
Else 
SELECT CONVERT(VARCHAR(10),DATEPART(hh,@SubtractDate))+ ' Hour(s) ' AS [Result]

编辑：复制从评论粘贴。

输入时：

SET @Hours=24 /* User input to have hours /

期望的结果将是：1 Day相反，它显示：1 Day(s) 0 Hour(s)

另外：输入时：

SET @Hours=48 / User input to have hours */ 

期望的结果将是：2 Days 相反，它显示：2 Day(s) 0 Hour(s)

Answer 1

使用

CASE 检查是否应使用小时部分。

查询

DECLARE @Hours AS INT
DECLARE @SubtractDate AS DATETIME
SET @Hours=24/* User input to have hours */
SET @SubtractDate=DATEADD(hh,@Hours,GETDATE()) - GETDATE()
if @Hours>=24

SELECT
  CONVERT(VARCHAR(10),DATEDIFF(DAY,'1900-01-01',@SubtractDate))+ ' Day(s) ' +
  + 
  CASE 
    WHEN CONVERT(VARCHAR(10),DATEPART(hh,@SubtractDate)) > 0
    THEN CONVERT(VARCHAR(10),DATEPART(hh,@SubtractDate)) + ' Hour(s)' 
    ELSE '' 
  END 
  AS [Result]
Else 
  SELECT
    CONVERT(VARCHAR(10),DATEPART(hh,@SubtractDate))+ ' Hour(s) '
  AS [Result]

结果集 @Hours=24

|    Result |
|-----------|
| 1 Day(s)  |

结果集 @Hours=25

|             Result |
|--------------------|
| 1 Day(s) 1 Hour(s) |

结果集 @Hours=48

|    Result |
|-----------|
| 2 Day(s)  |

编辑：输出格式

查询

DECLARE @Hours AS INT
DECLARE @SubtractDate AS DATETIME
SET @Hours=1/* User input to have hours */
SET @SubtractDate=DATEADD(hh,@Hours,GETDATE()) - GETDATE()
if @Hours>=24

SELECT
  CASE 
    WHEN CONVERT(VARCHAR(10),DATEDIFF(DAY,'1900-01-01',@SubtractDate)) = 1
    THEN CONVERT(VARCHAR(10),DATEDIFF(DAY,'1900-01-01',@SubtractDate))+ ' Day '
    WHEN CONVERT(VARCHAR(10),DATEDIFF(DAY,'1900-01-01',@SubtractDate)) >= 2 
    THEN CONVERT(VARCHAR(10),DATEDIFF(DAY,'1900-01-01',@SubtractDate))+ ' Days '
  END
  + 
  CASE 
    WHEN CONVERT(VARCHAR(10),DATEPART(hh,@SubtractDate)) > 0
    THEN CASE
           WHEN CONVERT(VARCHAR(10),DATEPART(hh,@SubtractDate)) = 1 
           THEN CONVERT(VARCHAR(10),DATEPART(hh,@SubtractDate)) + ' Hour'
           WHEN CONVERT(VARCHAR(10),DATEPART(hh,@SubtractDate)) >= 2 
           THEN CONVERT(VARCHAR(10),DATEPART(hh,@SubtractDate)) + ' Hours'           
         END
    ELSE '' 
  END 
  AS [Result]
Else 
  SELECT
  CASE 
    WHEN CONVERT(VARCHAR(10),DATEPART(hh,@SubtractDate)) > 0
    THEN CASE
           WHEN CONVERT(VARCHAR(10),DATEPART(hh,@SubtractDate)) = 1 
           THEN CONVERT(VARCHAR(10),DATEPART(hh,@SubtractDate)) + ' Hour'
           WHEN CONVERT(VARCHAR(10),DATEPART(hh,@SubtractDate)) >= 2 
           THEN CONVERT(VARCHAR(10),DATEPART(hh,@SubtractDate)) + ' Hours'           
         END
    ELSE '' 
  END 
  AS [Result]

结果集 @Hours=1

| Result |
|--------|
| 1 Hour |

结果集 @Hours=2

| Result  |
|---------|
| 2 Hours |

结果集 @Hours=24

| Result |
|--------|
| 1 Day  |

结果集 @Hours=25

|             Result |
|--------------------|
| 1 Day 1 Hour       |

结果集 @Hours=26

|        Result |
|---------------|
| 1 Day 2 Hours |

结果集 @Hours=48

|    Result |
|-----------|
| 2 Days    |