DateFormat issue in query.

Posted by Ashokn under Sql Server on 2/4/2011 | Points: 10 | Views : 1391 | Status : [Member] | Replies : 1
Hi,

With below qry I need to get records from Feb-01 to Feb -02(days) but I'm getting 3 rows.
Please suggest the change in below qry to avoid 3rd row(Res3- for 01/02/2011)
***Please note ;- DateTimeStamp in tables is stored in the format 12/23/2010 12:15:43 AM.
-------------------------------------------------------------------------------
select DD.DamName,round(avg(Convert(decimal(18,2),DAV.WaterLevelFt)),2) 'feet',
round(avg(round((Convert(decimal(18,2),DAV.WaterLevelFt))/3.28,2)),2) 'meter',
round(avg(isnull(Convert(decimal(18,2),LUT.Volume),0)),2) 'MCft',
Convert(varchar(10),DateTimeStamp,103) as DateTimeStamp from DAMSActualValues DAV
left join LookUpTable LUT on (LUT.SiteID = DAV.SiteID and LUT.WaterLevelFt = DAV.WaterLevelFt)
inner join DAMDetails DD on (DAV.siteid = DD.siteid)
where Convert(varchar(10),DateTimeStamp,103) between '01/02/2011' and '02/02/2011' and DAV.siteid=777702
group by Convert(varchar(10),DateTimeStamp,103),DD.DamName order by Convert(varchar(10),DateTimeStamp,103) desc

==>Below is the result of this query...
Res1 2278.590000 694.690000 6433.520000 02/02/2011
Res2 2278.340000 694.610000 6294.960000 02/01/2011
Res3 2278.690000 694.720000 6489.990000 01/02/2011




Responses

Posted by: T.saravanan on: 2/4/2011 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi Ashokn,

Small changes in your query....
select DD.DamName,round(avg(Convert(decimal(18,2),DAV.WaterLevelFt)),2) 'feet',

round(avg(round((Convert(decimal(18,2),DAV.WaterLevelFt))/3.28,2)),2) 'meter',
round(avg(isnull(Convert(decimal(18,2),LUT.Volume),0)),2) 'MCft',
Convert(varchar(10),DateTimeStamp,103) as DateTimeStamp from DAMSActualValues DAV
left join LookUpTable LUT on (LUT.SiteID = DAV.SiteID and LUT.WaterLevelFt = DAV.WaterLevelFt)
inner join DAMDetails DD on (DAV.siteid = DD.siteid)
where Convert(varchar(10),DateTimeStamp,103) between '02/01/2011' and '02/02/2011' and DAV.siteid=777702
group by Convert(varchar(10),DateTimeStamp,103),DD.DamName order by Convert(varchar(10),DateTimeStamp,103) desc


because in Sql Server dateformat is 'MM/dd/yyyy'

Try this....

Cheers :)


Thanks,
T.Saravanan

Ashokn, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response