Case When Dateadd problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Case When Dateadd problem

Author

Topic

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2009-01-06 : 09:43:02

Here is my code:

SELECT 
RCI.fpartno as PartNumber,
RCI.fpartrev as PartRev,
RCI.fdescript as PartDesc,
RCL.fdexpdate as ExpirationDate,
INM.fleadtime as DaysLeadTime, 
Case 
When DATEADD(dd, INM.FLEADTIME, RCL.FDEXPDATE) > getdate() then DATEADD(dd, INM.FLEADTIME, RCL.FDEXPDATE)
else 'PastDue'
End as ProjOrderDate
  FROM dbo.rclotc RCL 
	inner join dbo.rcitem RCI on RCL.fcrcitmkey = RCI.freceiver + RCI.fitemno
	inner join dbo.inmast INM on RCI.fpartno = INM.fpartno and RCI.fpartrev = INM.frev
 WHERE year(RCL.fdexpdate) > 1901
order by rcl.fdexpdate

When I run this I get the following error:

Error 1/6/2009 8:41:09 AM 0:00:02.578 SQL Server Database Error: Syntax error converting datetime from character string. 2 0

I'm assuming that I cannot use the ProjOrderDate to display both dates and a string. Any suggestions?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-06 : 09:47:16

what are you trying to evaluate in case expression? DATEADD(dd, INM.FLEADTIME, RCL.FDEXPDATE) actually adds INM.FLEADTIME days to RCL.FDEXPDATE and compares it to getdate. for this to work INM.FLEADTIME should be numerical data type like int.
also is RCL.FDEXPDATE datetime field?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-01-06 : 09:59:58

[code]case
when dateadd(dd, INM.FLEADTIME, RCL.FDEXPDATE) > getdate()
then convert(varchar(10),dateadd(dd, INM.FLEADTIME, RCL.FDEXPDATE),121)
else 'PastDue'
end as ProjOrderDate[/code]

CODO ERGO SUM

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2009-01-06 : 12:29:30

Thanks guys, this ended up being what I needed.

SELECT RCI.fpartno AS PartNumber,
       RCI.fpartrev AS PartRev,
       RCI.fdescript AS PartDesc,
       RCL.fdexpdate AS ExpirationDate,
       INM.fleadtime AS DaysLeadTime,
       CASE
          WHEN DATEADD (dd, -INM.FLEADTIME, RCL.FDEXPDATE) > getdate ()
          THEN
             convert (VARCHAR (10),
                      dateadd (dd, -INM.FLEADTIME, RCL.FDEXPDATE),
                      121
             )
          ELSE
             'Order Now'
       END
          AS ProjOrderDate
  FROM       dbo.rclotc RCL
          INNER JOIN
             dbo.rcitem RCI
          ON RCL.fcrcitmkey = RCI.freceiver + RCI.fitemno
       INNER JOIN
          dbo.inmast INM
       ON RCI.fpartno = INM.fpartno AND RCI.fpartrev = INM.frev
 WHERE year (RCL.fdexpdate) > 1901
ORDER BY rcl.fdexpdate

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-06 : 12:34:10

if you have an index on fdexpdate, you could modify it like below to use the index:-


SELECT RCI.fpartno AS PartNumber,
       RCI.fpartrev AS PartRev,
       RCI.fdescript AS PartDesc,
       RCL.fdexpdate AS ExpirationDate,
       INM.fleadtime AS DaysLeadTime,
       CASE
          WHEN DATEADD (dd, -INM.FLEADTIME, RCL.FDEXPDATE) > getdate ()
          THEN
             convert (VARCHAR (10),
                      dateadd (dd, -INM.FLEADTIME, RCL.FDEXPDATE),
                      121
             )
          ELSE
             'Order Now'
       END
          AS ProjOrderDate
  FROM       dbo.rclotc RCL
          INNER JOIN
             dbo.rcitem RCI
          ON RCL.fcrcitmkey = RCI.freceiver + RCI.fitemno
       INNER JOIN
          dbo.inmast INM
       ON RCI.fpartno = INM.fpartno AND RCI.fpartrev = INM.frev
 WHERE RCL.fdexpdate >= '19020101'
ORDER BY rcl.fdexpdate

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources