Link to home
Start Free TrialLog in
Avatar of NBPLSADMIN
NBPLSADMIN

asked on

Error with Datetime in Query

I have the following query.

select sys.netbios_name0,
  wi.ServiceTag0 [Service Tag],
  wi.provider0 [Provider],
  wi.shipdate0 [Ship Date],
  wi.Startdate0 [Warranty Start Date],
  wi.Enddate0 [Warranty End Date],
  GETDATE(),
 DateDiff("d",wi.Enddate0,GETDATE())[Warranty Days Remaining],
  wi.WarrantyExtension0 [Warranty Extension],
  wi.SystemType0 [System Type],
  wi.datescriptran0 [Timestamp data gathered],
  wi.dellIBU0,
  wi.Description0 as [Description]
from v_R_System as [SYS]
join v_gs_Warranty_Info0 as [WI] on sys.resourceid=wi.resourceid
order by sys.netbios_name0

The warranty end date is in the format MM/DD/YYYY except for a few fields where it could not collect the data, those fields show up as "--"
I'm sure I need a case statement in there where I bolded the line above to resolve the error, but I can't figure it out. Here's the error.

Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Thanks
Avatar of mkobrin
mkobrin
select sys.netbios_name0,
  wi.ServiceTag0 [Service Tag],
  wi.provider0 [Provider],
  wi.shipdate0 [Ship Date],
  wi.Startdate0 [Warranty Start Date],
  wi.Enddate0 [Warranty End Date],
  GETDATE(),
case wi.Enddate0 when '--' then 0 else
 DateDiff("d",wi.Enddate0,GETDATE())[Warranty Days Remaining] end as date_diff,
  wi.WarrantyExtension0 [Warranty Extension],
  wi.SystemType0 [System Type],
  wi.datescriptran0 [Timestamp data gathered],
  wi.dellIBU0,
  wi.Description0 as [Description]
from v_R_System as [SYS]
join v_gs_Warranty_Info0 as [WI] on sys.resourceid=wi.resourceid
order by sys.netbios_name0

Avatar of NBPLSADMIN
NBPLSADMIN

ASKER

Now I'm getting this error:

Msg 102, Level 15, State 1, Line 9
Incorrect syntax near 'Warranty Days Remaining'.

FYI there is no column called [Warranty Days Remaining] , what I'm trying to do with this line

DateDiff("d",wi.Enddate0,GETDATE())[Warranty Days Remaining]

is to have the date returned to a column that will be called [Warranty Days Remaining] in the table being displayed. Maybe I have this format wrong as well. I was following the format of the other fields like this one.

wi.Startdate0 [Warranty Start Date]

Avatar of mkobrin
mkobrin
Sorry, I meant to take that out before posting.

Try this

select sys.netbios_name0,
  wi.ServiceTag0 [Service Tag],
  wi.provider0 [Provider],
  wi.shipdate0 [Ship Date],
  wi.Startdate0 [Warranty Start Date],
  wi.Enddate0 [Warranty End Date],
  GETDATE(),
case wi.Enddate0 when '--' then 0 else
 DateDiff("d",wi.Enddate0,GETDATE()) end as date_diff,
  wi.WarrantyExtension0 [Warranty Extension],
  wi.SystemType0 [System Type],
  wi.datescriptran0 [Timestamp data gathered],
  wi.dellIBU0,
  wi.Description0 as [Description]
from v_R_System as [SYS]
join v_gs_Warranty_Info0 as [WI] on sys.resourceid=wi.resourceid
order by sys.netbios_name0

Avatar of Arthur_Wood
Arthur_Wood
Flag of United States of America image
would this work:

case wi.Enddate0 when '--' then 0 else
 DateDiff("d",wi.Enddate0,GETDATE())
end as [Warranty Days Remaining]

AW
ASKER CERTIFIED SOLUTION
Avatar of mkobrin
mkobrin
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of NBPLSADMIN
NBPLSADMIN

ASKER

Thanks that worked perfectly.

For the Warranty Days Remaining, I want it to show 0 if the value is less than 0. Obviously, if the warranty is expired, I have 0 days remaining, not -56 for example. I know this is on top of the question asked, but you seem on a roll.

Following your format I tried
case [Warranty Days Remaining] when <0 then 0 else [Warranty Days Remaining] end,
But then I get:
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near '<'.

So with some more looking up on the case syntax I tried:
case when [Warranty Days Remaining]<0 then 0 else [Warranty Days Remaining] end,
which produces:
Msg 207, Level 16, State 1, Line 9
Invalid column name 'Warranty Days Remaining'.
Msg 207, Level 16, State 1, Line 9
Invalid column name 'Warranty Days Remaining'.

I thought I was pretty good with SQL but I guess I still have lots to learn.



Avatar of mkobrin
mkobrin
I'm not sure if this will work, but you can try it:

select sys.netbios_name0,
  wi.ServiceTag0 [Service Tag],
  wi.provider0 [Provider],
  wi.shipdate0 [Ship Date],
  wi.Startdate0 [Warranty Start Date],
  wi.Enddate0 [Warranty End Date],
  GETDATE(),
case wi.Enddate0 when '--' then 0 else
 case DateDiff("d",wi.Enddate0,GETDATE()) when < 0 then 0 else  DateDiff("d",wi.Enddate0,GETDATE())  end end as [Warranty Days Remaining] ,
  wi.WarrantyExtension0 [Warranty Extension],
  wi.SystemType0 [System Type],
  wi.datescriptran0 [Timestamp data gathered],
  wi.dellIBU0,
  wi.Description0 as [Description]
from v_R_System as [SYS]
join v_gs_Warranty_Info0 as [WI] on sys.resourceid=wi.resourceid
order by sys.netbios_name0

Avatar of NBPLSADMIN
NBPLSADMIN

ASKER

You just about had it.

Had to move the "when" statement

case DateDiff("d",wi.Enddate0,GETDATE()) when < 0 then 0 else  DateDiff("d",wi.Enddate0,GETDATE())  end end as [Warranty Days Remaining] ,

to

case when DateDiff("d",GETDATE(),wi.Enddate0)  < 0 then 0 else DateDiff("d",GETDATE(),wi.Enddate0)  end end as [Warranty Days Remaining] ,

Thanks again for your help.
Avatar of mkobrin
mkobrin
No problem, all the best with your learning of SQL.