I am modifying an existing request for a client, and I ran into some problem.
Our client uses SQL Server 2008 R2, and the corresponding database enables the user to specify custom fields for one of their tables using the EAV structure. All values stored in this structure, varchar(255)and some of the fields are for storing dates. This question is modified to use two of these fields and compare them (one is the start, the other is the end) against the current date to determine which row is "current".
The problem I am facing is that part of the request is executing CONVERT(DateTime, eav.Value)to turn varcharinto DateTime. The conversions themselves completely succeeded, and I can include this value as part of the sentence SELECT, but part of the question gives me a conversion error:
Conversion failed when converting date and/or time from character string.
The real kicker is this: if I define the base for this query (getting a list of entities with two custom field values flattened on one line) as the view and select against the view and filtering the view getdate(), then it works correctly, but it fails if I add Joining a second table using one of the (non-date) fields from the view. I understand that this can be a little complicated, so I can send an approximate request if necessary, but this question is already a bit long.
I tried to recreate the base structure in another database and include sample data, but the new database behaves as expected, so I don’t understand here.
EDIT In case this is useful, here is the instruction for presentation:
create view Festival as
select
e.EntityId as FestivalId,
e.LookupAs as FestivalName,
convert(Date, nvs.Value) as ActivityStart,
convert(Date, nve.Value) as ActivityEnd
from tblEntity e
left join CustomControl ccs on ccs.ShortName = 'Activity Start Date'
left join CustomControl cce on cce.ShortName = 'Activity End Date'
left join tblEntityNameValue nvs on nvs.CustomControlId = ccs.IdCustomControl and nvs.EntityId = e.EntityId
left join tblEntityNameValue nve on nve.CustomControlId = cce.IdCustomControl and nve.EntityId = e.EntityId
where e.EntityType = 'Festival'
The unsuccessful request is as follows:
select *
from Festival f
join FestivalAttendeeAll fa on fa.FestivalId = f.FestivalId
where getdate() between f.ActivityStart and f.ActivityEnd
But it works:
select *
from Festival f
where getdate() between f.ActivityStart and f.ActivityEnd
( EntityId/ FestivalIdare int columns)