比较两个日期在Sql中 [英] Comparing Two Dates in Sql

问题描述

我将TrnDteDeliveryDate更改为dd mmm yyyy格式，并将TrnDteRcvDate更改为dd mmm yyyy格式

我需要检查TrnDteDeliveryDate> TrnDteRcvDate.如果TrnDteDeliveryDate大于TrnDteRcvDate，则声明be Before Delivered

我使用了案例声明

在(CONVERT(VARCHAR，TrnDteDeliveryDate，106)&>; CONVERT(VARCHAR，TrnDteRcvDate，106))的情况下``在交付之前''否则``在推导之后''

例如:

TrnDteDeliveryDate是2013年7月2日，TrnDteDeliveryDAte是2013年5月26日.在这种情况下，它应该在交付之前，但是在交付之后才显示.

请做必要的操作

Hi,

I changed TrnDteDeliveryDate as dd mmm yyyy format and TrnDteRcvDate as dd mmm yyyy format

I need to check TrnDteDeliveryDate > TrnDteRcvDate . If TrnDteDeliveryDate is greater than TrnDteRcvDate then statement be Before Delivered

I used case statement

CASE WHEN (CONVERT(VARCHAR, TrnDteDeliveryDate, 106) > CONVERT(VARCHAR, TrnDteRcvDate, 106)) THEN ''Before delivered'' else ''After deivered''

Eg:

TrnDteDeliveryDate is 02 Jul 2013 and TrnDteDeliveryDAte is 26 may 2013 . In this case it should be before Delivered but it is showing after deivered.

please do needful

推荐答案

首先，不要存储这样的日期-始终将日期始终存储为Date或DateTime字段-这样您就不会这样做"不必费力地进行转换，并且数据变得更加稳定，因为它较少依赖于用户输入数据的方式...

其次，我只是尝试过:

First off, don''t store dates like that - always, always store dates as a Date or DateTime field - that way you don''t have to faff around with converting, and your data becomes more stable, as it is less dependant on how the user enters it...

Secondly, I just tried it:

USE [Testing]
GO

/****** Object:  Table [dbo].[T2]    Script Date: 09/28/2013 12:19:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[T2](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [RD] [nchar](20) NOT NULL,
    [DD] [nchar](20) NOT NULL
) ON [PRIMARY]

GO

Id      RD                      DD
1       02 Jul 2013             26 may 2013         
3       26 may 2013             02 Jul 2013         

SELECT Id,
CASE WHEN (CONVERT(VARCHAR, RD, 106) > CONVERT(VARCHAR, DD, 106)) THEN 'Before delivered' else 'After deivered' END
FROM T2

并得到:

Id      (No column name)
1       After deivered
3       Before delivered

这正是我所期望的.

所以，这不是代码，而是您的数据...

Which is exactly what I would expect.

So, it''s not the code - it''s your data...

这篇关于比较两个日期在Sql中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！