| Author |
Topic |
|
manisha gangwar
Starting Member
3 Posts |
 Posted - 2010-07-29 : 06:14:05
|
hii,,
i have problem in fetching dates that are in dd/mm/yyyy format.
i m using query--
select * from tborc where date between '01/07/2010' and '11/07/2010'
the problem is that this query is not comparing months , it is comparing dates only.
like if date field has following data--
date(varchar)
----------
02/07/2010
10/07/2010
05/08/2010
then it should not take 05/08/2010.
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-07-29 : 07:11:07
|
You should really store dates as datatype datetime and format it only when displaying.
But to your problem
select * from tborc where convert(datetime,date,104) between convert(datetime,'01/07/2010',104) and convert(datetime,'11/07/2010',104)
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-07-29 : 07:11:47
|
the problem is because you are storing date in varchar. Why not using datatime data type ?
KH
[spoiler]Time is always against us[/spoiler]
|
|
|
|
manisha gangwar
Starting Member
3 Posts |
Posted - 2010-07-29 : 08:41:28
|
| thanx ,, it worked |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-07-29 : 08:42:10
|
welcome 
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
manisha gangwar
Starting Member
3 Posts |
Posted - 2010-07-29 : 08:43:08
|
| as my pl told so! |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-07-29 : 08:50:44
|
quote:
Originally posted by manisha gangwar
as my pl told so!
I think this is the answer to
Why not using datatime data type?
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
navi1478
Starting Member
2 Posts |
Posted - 2013-06-11 : 02:50:29
|
select * from tborc where convert(datetime,date,104) between convert(datetime,'01/07/2010',104) and convert(datetime,'11/07/2010',104)
I used this same query but create some issues
like
'01/07/2010' and '01/09/2010'
not showing 08 & 09 months data ..
Navi |
|
|
|
navi1478
Starting Member
2 Posts |
Posted - 2013-06-11 : 02:57:17
|
Sorry this query is working fine .
error on my side
Thnx webfred
Navi |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-11 : 03:18:12
|
quote:
Originally posted by navi1478
select * from tborc where convert(datetime,date,104) between convert(datetime,'01/07/2010',104) and convert(datetime,'11/07/2010',104)
I used this same query but create some issues
like
'01/07/2010' and '01/09/2010'
not showing 08 & 09 months data ..
Navi
this will cause the optimiser to ignore an idex if already present on date field
see
http://visakhm.blogspot.in/2012/12/different-ways-to-implement-date-range.html
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
|
cannot fetch dates in dd/mm/yyyy