[MSSQL] Vergelijken van DateTime

[renekosterman]

Ik zit met het probleem van het vergelijken van DateTime velden die net zijn geconvert.

Ik heb de volgende code:

code:

SELECT     dbo.TBL_Value.ItemId AS id, dbo.TBL_Value.VarValue, CONVERT(DATETIME, CONVERT(varchar(1000), dbo.TBL_Value.VarValue), 101) AS Expr1, 
                      CONVERT(DATETIME, '2005-01-01', 101) AS Expr2
FROM         dbo.TBL_Variable INNER JOIN
                      dbo.TBL_Value ON dbo.TBL_Variable.Id = dbo.TBL_Value.VariableId
WHERE     (dbo.TBL_Variable.Name = 'Datum') AND (dbo.TBL_Variable.TemplateID = 20085)

Deze werkt perfect daar heb ik geen problemen mee, maar als ik nou een voorwaarde zet op Expr1 bijvoorbeeld:

code:

SELECT     dbo.TBL_Value.ItemId AS id, dbo.TBL_Value.VarValue, CONVERT(DATETIME, CONVERT(varchar(1000), dbo.TBL_Value.VarValue), 101) AS Expr1, 
                      CONVERT(DATETIME, '2005-01-01', 101) AS Expr2
FROM         dbo.TBL_Variable INNER JOIN
                      dbo.TBL_Value ON dbo.TBL_Variable.Id = dbo.TBL_Value.VariableId
WHERE     (dbo.TBL_Variable.Name = 'Datum') AND (dbo.TBL_Variable.TemplateID = 20085) AND (CONVERT(DATETIME, CONVERT(varchar(1000), 
                      dbo.TBL_Value.VarValue), 101) < CONVERT(DATETIME, '2005-01-01', 105))

Dan krijg ik de volgende foutmelding: "Syntax error converting datetime from character string.", Nou heb ik me suf gezocht, maar ik kan niet vinden waarom dit een foutmelding zou moeten geven, aangezien de convert gewoon goed is

Kan iemand mij hier mee helpen?

edit:
Het resultaat van Expr1 is: 1-1-2004 en van Expr2 is 1-1-2005

[ Voor 6% gewijzigd door renekosterman op 12-10-2005 13:25 ]

[P_de_B]

dbo.TBL_Value.VarValue bevat een waarde die niet omgezet kan worden naar een datum. Kun je controleren welke waarde?

[renekosterman]

De waarde van VarValue is 01-01-2004

[curry684]

Zo te zien een waarde in dbo.TBL_Value.VarValue die niet naar DateTime te converteren is

edit:
*traag*

Maaruhm is dat de enige waarde in die hele tabel?

[ Voor 31% gewijzigd door curry684 op 12-10-2005 13:29 ]

[renekosterman]

Hij is blijkbaar toch te converteren anders zou die in de eerste query ook een foutmelding moeten geven.

@curry684: Nee er staan ook andere waardes in, deze worden echter niet geselecteerd.

[ Voor 30% gewijzigd door renekosterman op 12-10-2005 13:31 ]

[aex351]

Je hebt wel vervelende tabel namen zeg.

[curry684]

downtime schreef op woensdag 12 oktober 2005 @ 13:29:
Hij is blijkbaar toch te converteren anders zou die in de eerste query ook een foutmelding moeten geven.

Dit werkt bij mij gewoon:

SQL:

select CONVERT(DATETIME, CONVERT(varchar(1000), '01-01-2004'), 101) 
select CONVERT(DATETIME, '2005-01-01', 105)

Zelfs dit werkt:

SQL:

if(CONVERT(DATETIME, CONVERT(varchar(1000), '01-01-2004'), 101) <
   CONVERT(DATETIME, '2005-01-01', 105))
  select 'Smaller';
else
  select 'Larger';

Dus er moet nog een andere waarde in die tabel staan zo ongeveer

[renekosterman]

aex351 schreef op woensdag 12 oktober 2005 @ 13:30:
Je hebt wel vervelende tabel namen zeg.

Tja...het is niet mijn tabel, en zo vervelend vind ik ze niet hoor.

[curry684]

downtime schreef op woensdag 12 oktober 2005 @ 13:29:
Hij is blijkbaar toch te converteren anders zou die in de eerste query ook een foutmelding moeten geven.

@curry684: Nee er staan ook andere waardes in, deze worden echter niet geselecteerd.

Nee maar als je ze in je filter gebruikt wordt die conversie natuurlijk wel voor alle rows gebruikt omdat ie anders niet weet hoe ie ze moet filteren he

Kijk voor de lol eens naar je execution plan (ctrl-L).

[ Voor 6% gewijzigd door curry684 op 12-10-2005 13:34 ]

[P_de_B]

Wat kijg je als je het volgende doet? Dezelfde foutmelding?

SQL:

SELECT CONVERT(datetime, dbo.TBL_Value.VarValue, 101) FROM tabel 

En conroleer wel even alle records, ook al vallen ze buiten de where!

[ Voor 20% gewijzigd door P_de_B op 12-10-2005 13:34 ]

[renekosterman]

@curry684: Ik ga even kijken wat er nou allemaal in de database staat.
@P_de_B: Deze SQL die jij gebruikt kan niet, omdat VarValue een 'text' veld is.

/Edit:
Er is nog een rij waar de beschrijving in staat, maar ik vind het wel raar aangezien ik specifiek aan geef dat de Name 'Datum' moet zijn.

[ Voor 36% gewijzigd door renekosterman op 12-10-2005 13:40 ]

[P_de_B]

downtime schreef op woensdag 12 oktober 2005 @ 13:36:

@P_de_B: Deze SQL die jij gebruikt kan niet, omdat VarValue een 'text' veld is.

Kun je dat niet veranderen dan? Je hebt toch geen TEXT nodig daar? Als het kan moet je TEXT echt vermijden.

Anyway, hoe gaat het dan met de extra convert naar een varchar?

[renekosterman]

Nee dat kan niet veranderen, omdat deze zowel Datum velden als normale tekst kan bevatten. De conversie van text naar varchar is nodig omdat een varchar wel mag worden omgezet naar een datetime.

Als ik de volgende code uitvoer (en dus niet meegeef dat hij alleen het veld moet pakken waar Name = 'Datum') dan krijg ik wel weer de bovenstaande error zoals in de topic start

SQL:

SELECT     dbo.TBL_Value.ItemId AS id, dbo.TBL_Value.VarValue, CONVERT(DATETIME, CONVERT(varchar(1000), dbo.TBL_Value.VarValue), 101) AS Expr1, 
                      CONVERT(DATETIME, '2005-01-01', 101) AS Expr2, dbo.TBL_Variable.Name
FROM         dbo.TBL_Variable INNER JOIN
                      dbo.TBL_Value ON dbo.TBL_Variable.Id = dbo.TBL_Value.VariableId
WHERE     (dbo.TBL_Variable.TemplateID = 20085)

[ Voor 62% gewijzigd door renekosterman op 12-10-2005 13:44 ]

[P_de_B]

downtime schreef op woensdag 12 oktober 2005 @ 13:42:
Nee dat kan niet veranderen, omdat deze zowel Datum velden als normale tekst kan bevatten. De conversie van text naar varchar is nodig omdat een varchar wel mag worden omgezet naar een datetime.

Hmm, het begint erop te lijken dat het ontwerp van je database te wensen over laat. Maar kan de kolom tekst bevatten van meer dan 8000 karakters? Immers pas dan heb je TEXT nodig in plaats van VARCHAR(8000).

Maar goed, je zit dus met een probleem omdat voor het toepassen van het filter _alle_ records geconverteerd moeten worden. Hoe weet de database anders of een record aan het selectiecriterium voldoet?

[renekosterman]

Ja deze moeten meer dan 8000 karakters kunnen bevatten. Het is nogal een groot project waar verschillende gegevens na de hand van templates in kunnen worden gezet.

Dan blijft de vraag waarom kan hij deze in de eerste query wel converten?

[P_de_B]

downtime schreef op woensdag 12 oktober 2005 @ 13:47:

Dan blijft de vraag waarom kan hij deze in de eerste query wel converten?

Omdat je dan in de WHERE expliciet een harde waarde geeft die wel een datum is. Als je de convert naar de WHERE plaats (zoals in de 2e) moet de convert op alle waardes van de kolom uitgevoerd worden. Dus ook op de records die helemaal geen datum bevatten.

[curry684]

Klassiek gevalletje verneukt DB-design idd

[renekosterman]

curry684 schreef op woensdag 12 oktober 2005 @ 13:57:
Klassiek gevalletje verneukt DB-design idd

[zeurmodus] Over het database design is echt wel nagedacht hoor, vind het een beetje een loze opmerking als je niet weet wat de achterliggende gedachte van het project is. [/zeurmodus]

Maar we gaan nog even veder denken hiero. bedankt alvast.

[P_de_B]

downtime schreef op woensdag 12 oktober 2005 @ 14:02:
[...]


[zeurmodus] Over het database design is echt wel nagedacht hoor, vind het een beetje een loze opmerking als je niet weet wat de achterliggende gedachte van het project is. [/zeurmodus]

Maar we gaan nog even veder denken hiero. bedankt alvast.

Hoewel niet subtiel gebracht heeft Curry wel een punt. In mijn ogen is een correct design heel belangrijk. Als je design niet klopt loop je in de loop van de tijd tegen allemaal problemen aan. Zoals nu bijvoorbeeld. Ik zou je toch de tip willen geven er nog eens naar te kijken. Misschien kun je een tipje van de sluier oplichten? Over het algemeen wil men hier ook wel een beetje meedenken

[Reinier]

downtime schreef op woensdag 12 oktober 2005 @ 14:02:
[...]


[zeurmodus] Over het database design is echt wel nagedacht hoor, vind het een beetje een loze opmerking als je niet weet wat de achterliggende gedachte van het project is. [/zeurmodus]

Volgens mij is de achterliggende gedachte niet relevant. Een databasedesign waarbij een veld óf een datum óf een willekeurige lap tekst van meer dan 8000 karakters kan bevatten deugt simpelweg niet

[renekosterman]

Oke ik zal het zo goed mogelijk proberen uit te leggen:
Het is met onze software mogelijk om zelf Templates te maken voor bijv een dossier. Elke template kan variables (TBL_Variable) bevatten, welke door de gebruiker zelf kunnen worden gedefineerd. Zodra er een item (TBL_Item) word aangemaakt, zal deze gebruik maken van de variablen van zijn Template.

Wanneer er een variable word ingevuld zal deze worden opgeslagen in TBL_VarValue.

Hoop dat jullie begrijpen wat ik bedoel.

[curry684]

Ja zo begrijp ik het wel, je hebt dus een metabase ipv een database. Lovenswaardig, maar vervolgens moet je SQL niet gaan toepassen op de metabase maar op de database. Anders gezegd: in SQL niet aannames gaan doen over de inhoud van de database terwijl je op de metabase zit, en in jouw metabase is dat een text-field dat je niet naar datetime mag converteren. Handigste om met subqueries of views ervoor te zorgen dat je alleen de fields hebt waarvan de type_id aangeeft dat het datetime data is, en die vervolgens te converteren. Geloof me dat het in SQL Server geen procentje CPU power meer kost om eerst de goede data te filteren alvorens je hem gaat gebruiken

[renekosterman]

Oke ik denk dat ik de oplossing heb gevonden.

Ik moet ook nog het VariableID meegeven dan werkt het.

SQL:

SELECT     dbo.TBL_Value.ItemId AS id, dbo.TBL_Value.VarValue, CONVERT(DATETIME, CONVERT(varchar(1000), dbo.TBL_Value.VarValue), 101) AS Expr1, 
                      CONVERT(DATETIME, '2005-01-01', 101) AS Expr2, dbo.TBL_Value.VariableId
FROM         dbo.TBL_Variable INNER JOIN
                      dbo.TBL_Value ON dbo.TBL_Variable.Id = dbo.TBL_Value.VariableId
WHERE     (dbo.TBL_Variable.TemplateID = 20085) AND (CONVERT(DATETIME, CONVERT(varchar(1000), dbo.TBL_Value.VarValue), 101) < CONVERT(DATETIME, 
                      '2005-01-01', 105)) AND (dbo.TBL_Value.VariableId = 40284)

[renekosterman]

Oke het probleem is nog steeds niet opgelost.

We maken nu gebruik van twee SELECT's in onze query:

SQL:

SELECT     TOP 100 PERCENT NumberOfItems.id, COUNT(NumberOfItems.id) / 3.0 AS perc, dbo.TBL_Item.Description, dbo.TBL_Tree.Id AS TreeID
FROM         (SELECT     dbo.TBL_Value.ItemId AS id
                       FROM          dbo.TBL_Variable INNER JOIN
                                              dbo.TBL_Value ON dbo.TBL_Variable.ID = dbo.TBL_Value.VariableId
                       WHERE      (dbo.TBL_Value.VarValue LIKE '<geef hier de omschrijving in>' AND dbo.TBL_Variable.Name = 'description' AND 
                                              tbl_variable.templateId = 20085) OR
                                              (CONVERT(VARCHAR(255), dbo.TBL_Value.VarValue) < '01' AND dbo.TBL_Variable.Name = 'testnumeriek' AND 
                                              tbl_variable.templateId = 20085) OR
                                              (CONVERT(DATETIME, CONVERT(VARCHAR(255), dbo.TBL_Value.VarValue), 101) < CONVERT(DATETIME, '01-01-2005', 101) AND 
                                              dbo.TBL_Variable.Name = 'Datum' AND dbo.TBL_Variable.Id = '40284' AND tbl_variable.templateId = 20085)
                       GROUP BY dbo.TBL_Variable.Name, dbo.TBL_Value.ItemId) NumberOfItems INNER JOIN
                      dbo.TBL_Item ON NumberOfItems.id = dbo.TBL_Item.Id INNER JOIN
                      dbo.TBL_Tree ON dbo.TBL_Item.Id = dbo.TBL_Tree.ItemId
GROUP BY NumberOfItems.id, dbo.TBL_Item.Description, dbo.TBL_Tree.Id, dbo.TBL_Tree.TreePath, dbo.TBL_Item.FiltersetId
HAVING      (dbo.TBL_Tree.TreePath LIKE '20000,20167%')

Nu is het probleem dat de tweede select die zich in de eerste bevind, wel gewoon afzonderlijk werkt.

Maar draai ik de code zoals deze hierboven staat dan krijg ik weer de syntax error zoals in de start post.

[Woy]

Waarom heb je negens ( zoals curry684 al voorsteld ) een Veld wat aangeeft wat voor Type er in een veld zit. Dan kan je eerst filteren op TypeID en daarna kan je gaan converteren. Dan heb je ( Mits de database goed gevuld wordt ) nooit problemen dat een veld niet goed geconverteerd kan worden.

[renekosterman]

rwb schreef op donderdag 13 oktober 2005 @ 09:24:
Waarom heb je negens ( zoals curry684 al voorsteld ) een Veld wat aangeeft wat voor Type er in een veld zit. Dan kan je eerst filteren op TypeID en daarna kan je gaan converteren. Dan heb je ( Mits de database goed gevuld wordt ) nooit problemen dat een veld niet goed geconverteerd kan worden.

Zie mijn uitleg een paar posts hierboven.

Probleem is inmiddels opgelost het had te maken met de volgorde waar in de voorwaardes stelde in de WHERE.