出发日期大于抵达日期

Question

如何确保我的出发日期大于下列代码中的到达日期。

SELECT ArrivalDate, DATEADD(day, RAND(checksum(NEWID()))*1.5 
* LengthOfStay.LengthofStay, ArrivalDate) AS DepartureDate  
FROM Bookings, LengthOfStay 
ORDER BY ArrivalDate 

感谢

韦恩

Answer 1

尝试此查询

SELECT * FROM 
    (
     SELECT 
      ArrivalDate, 
      DATEADD(day, RAND(checksum(NEWID()))*1.5 * LengthOfStay.LengthofStay, ArrivalDate) AS DepartureDate  
     FROM 
      Bookings, LengthOfStay 
    ) a 
WHERE a.DepartureDate > a.ArrivalDate 
ORDER BY a.ArrivalDate 

Answer 2

DATEADD取整数...任何十进制值返回从随机将只被截断。因此，您可能只需将0添加到ArrivalDate，结果两个日期相等。

你可以仅仅通过增加最少的1您随机解决这个问题：

SELECT ArrivalDate, DATEADD(day, 1 + RAND(checksum(NEWID()))*1.5 
* LengthOfStay.LengthofStay, ArrivalDate) AS DepartureDate  
FROM Bookings, LengthOfStay 
ORDER BY ArrivalDate