Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
1sabine8
Posting Yak Master
130 Posts |
 Posted - 2009-05-29 : 06:05:37
|
| Hi,I am building the query SELECT count(fcc.new_customeridname)from Customer fcc WHERE fcc.time = 'First Time' AND fcc.new_date between '01/20/2008' and '01/01/2010'It's not returning any data although i have them. so i guess it's the conversion of the date issue. the date in the db is saved as follows 5/25/2009 12:00:00 AM. so how can i do the conversion in the query to get data?Thanks in advance |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-05-29 : 06:07:15
|
what's the data type for new_date ? varchar or datetime ?
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
1sabine8
Posting Yak Master
130 Posts |
Posted - 2009-05-29 : 06:12:21
|
| It's a datetime field |
|
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2009-05-29 : 06:18:53
|
| and dateadd(d,datediff(d,0,fcc.new_date),0) between '01/20/2008' and '01/01/2010' |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-05-29 : 06:27:00
|
quote:
Originally posted by bklr
and dateadd(d,datediff(d,0,fcc.new_date),0) between '01/20/2008' and '01/01/2010'
And spoil the index over new_date column?and fcc.new_date >= '20080120' fcc.new_date < '20100101'
E 12°55'05.63"N 56°04'39.26" |
|
|
|
1sabine8
Posting Yak Master
130 Posts |
Posted - 2009-05-29 : 06:34:38
|
| Sorry i didn't get your index question.What should i use exactly? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-05-29 : 07:16:01
|
If you have an index over new_date column (and you probably should), bklr's suggestion makes your index useless.My suggestion gives you the advantage to use an existing index over new_date column.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
|
|
|
Datetime conversion