时间戳

首页 > 解决方案 > ROW_NUMBER - 从字符串转换日期和/或时间时转换失败

sql-server - ROW_NUMBER - 从字符串转换日期和/或时间时转换失败

问题描述

这是我的表架构

CREATE TABLE [dbo].[MYTBL](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [Designation] [nvarchar](250) NULL,
    [LastModifiedDate] [datetime] NULL,
    [Score] [INT] NULL,
    [CreatedDate] [datetime] NOT NULL
 CONSTRAINT [PK_OP_ProspectTBL] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO MYTBL VALUES ('a', GETUTCDATE(), 1, GETUTCDATE());
INSERT INTO MYTBL VALUES ('O', GETUTCDATE(), 112, GETUTCDATE());
INSERT INTO MYTBL VALUES ('Ob', GETUTCDATE(), 12, GETUTCDATE());
select * from MYTBL;

询问

DECLARE @IN_ORDERBY_COL int = 3, 
        @IN_ORDERBY_DIRECTION int  = 1, 
        @IN_PAGENEXT int = 50, 
        @IN_PAGESKIP int = 0;

SELECT  ROW_NUMBER() OVER(ORDER BY 
                    (CASE WHEN @IN_ORDERBY_DIRECTION = 1 THEN
                        (
                            CASE 
                                WHEN @IN_ORDERBY_COL = 1 THEN P.CreatedDate
                                WHEN @IN_ORDERBY_COL = 2 THEN P.LastModifiedDate
                                WHEN @IN_ORDERBY_COL = 3 THEN P.Designation
                                WHEN @IN_ORDERBY_COL = 4 THEN P.Score
                            END
                        ) 
                    END) ASC,
                    (CASE WHEN @IN_ORDERBY_DIRECTION = 2 THEN
                        (
                            CASE 
                                WHEN @IN_ORDERBY_COL = 1 THEN P.CreatedDate
                                WHEN @IN_ORDERBY_COL = 2 THEN P.LastModifiedDate
                                WHEN @IN_ORDERBY_COL = 3 THEN P.Designation
                                WHEN @IN_ORDERBY_COL = 4 THEN P.Score
                            END
                        ) 
                    END) DESC
                ) AS [row_num],
COUNT(*) OVER () AS [total_rows],
*
FROM [dbo].[MYTBL] P WITH(NOLOCK)
ORDER BY row_num OFFSET @IN_PAGESKIP ROWS FETCH NEXT @IN_PAGENEXT ROWS ONLY;

上面的查询是一个存储过程，我正在尝试基于 Column 进行排序。

问题在于 Sort 中的所有列（2 个是DATETIMEtype ，另一个是NVARCHAR(10)）

小提琴链接

当我尝试按公司订购时，出现以下错误

从字符串转换日期和/或时间时转换失败。

标签： sql-server

解决方案

好吧，将日期转换为字符串：

DECLARE @IN_ORDERBY_COL int = 3, 
        @IN_ORDERBY_DIRECTION int  = 1, 
        @IN_PAGENEXT int = 50, 
        @IN_PAGESKIP int = 0;

SELECT  ROW_NUMBER() OVER(ORDER BY 
                    (CASE WHEN @IN_ORDERBY_DIRECTION = 1 THEN
                        (
                            CASE 
                                WHEN @IN_ORDERBY_COL = 1 THEN CONVErT(VARCHAR(32), P.CreatedDate, 121)
                                WHEN @IN_ORDERBY_COL = 2 THEN CONVErT(VARCHAR(32),P.LastModifiedDate, 121)
                                WHEN @IN_ORDERBY_COL = 3 THEN P.Designation
                            END
                        ) 
                    END) ASC,
                    (CASE WHEN @IN_ORDERBY_DIRECTION = 2 THEN
                        (
                            CASE 
                                WHEN @IN_ORDERBY_COL = 1 THEN CONVErT(VARCHAR(32),P.CreatedDate, 121)
                                WHEN @IN_ORDERBY_COL = 2 THEN CONVErT(VARCHAR(32),P.LastModifiedDate, 121)
                                WHEN @IN_ORDERBY_COL = 3 THEN P.Designation
                            END
                        ) 
                    END) DESC
                ) AS [row_num],
COUNT(*) OVER () AS [total_rows],
*
FROM [dbo].[MYTBL] P WITH(NOLOCK)
ORDER BY row_num OFFSET @IN_PAGESKIP ROWS FETCH NEXT @IN_PAGENEXT ROWS ONLY

;

推荐阅读