Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C#2.0 SQL VB.NET Server
The Query
"Select * from ( SELECT (convert(varchar, [InTime], 103))as Date FROM CarEnteries
Union SELECT (convert(varchar, [OutTime], 103)) as Date FROM CarEnteries)
a order by (convert(DateTime, [Date], 103)) asc"
 
Is working absolutly fine, when i am taking union of two date feilds in a single table but...
when i need range of date from the result i am getting error plxz help...
query is as follow
 

Select * from ( SELECT (convert(varchar, [InTime], 103))as Date FROM CarEnteries
Union SELECT (convert(varchar, [OutTime], 103)) as Date FROM CarEnteries)
Where (convert(DateTime, [Date], 103)) BETWEEN '08-05-2012' and '01-07-2012'
a order by (convert(DateTime, [Date], 103)) asc
 

Getting error message as
"Incorrect syntax near the keyword 'Where'."
 
[edit]Code blocks sorted out - OriginalGriff[/edit]
Posted 17-Jun-12 21:02pm
Edited 17-Jun-12 21:08pm
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Take the "a" out:
Select * from ( SELECT (convert(varchar, [InTime], 103))as Date FROM CarEnteries
Union SELECT (convert(varchar, [OutTime], 103)) as Date FROM CarEnteries)
Where (convert(DateTime, [Date], 103)) BETWEEN '08-05-2012' and '01-07-2012'
a order by (convert(DateTime, [Date], 103)) asc
becomes
Select * from ( SELECT (convert(varchar, [InTime], 103))as Date FROM CarEnteries
Union SELECT (convert(varchar, [OutTime], 103)) as Date FROM CarEnteries)
Where (convert(DateTime, [Date], 103)) BETWEEN '08-05-2012' and '01-07-2012'
order by (convert(DateTime, [Date], 103)) asc
 

BTW: never, ever store dates as strings. Store them as DateTime instead, then you don't have to faff with converting them every time you need to do any math. It also makes life a lot easier, because you move the internationalization to as close to input and output as possible - so the data in your DB is in a completely independent format.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

There is a syntax error with your query.
There is an extra barcket at the end of carEnteries.
 
However, note that your query will still not work (after removing this bracket).
 
When you do a UNION, the columns returned in all the constituents of the UNION queries should return the same set of columns.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

if ur [date] field datatype is datetime then use this query it will work
 
Select * from ( SELECT (convert(varchar, [InTime], 103))as Date FROM CarEnteries
Union SELECT (convert(varchar, [OutTime], 103)) as Date FROM CarEnteries)
Where [Date] BETWEEN '08-05-2012' and '01-07-2012'
order by [Date] asc
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 280
1 Sergey Alexandrovich Kryukov 279
2 CPallini 205
3 Maciej Los 162
4 Afzaal Ahmad Zeeshan 160
0 OriginalGriff 5,635
1 DamithSL 4,496
2 Maciej Los 3,942
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,180


Advertise | Privacy | Mobile
Web02 | 2.8.141216.1 | Last Updated 18 Jun 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100