DateFormat issue in query.

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

Latest Posts