mardi 10 février 2015

MS SQL Query criteria - need to extract date from datetime

I can't for the life of me get this to work. I've tried just about every example I've seen on here and all over the internet. I'm trying to query based on a month's worth of data. The data in the view I'm querying is in this format: 2012-03-20 00:00:00.000. Using the following criteria it's bringing back all of the dates (or nearly all of them). Any ideas?



AND cast(convert(varchar(10),lag.postingdate,112) as datetime) between '2015-01-01' and '2015-01-31'






Original Query:
SELECT prov.pgrp_id AS PERFORMING_PROV_ID
, prov.pgrp_prov_name AS PERFORMING_PROV_NAME
, lag.chgno AS CHARGE_NUM
, lag/countcharges AS LAGTIME
, lag.chgamt
, lag.postingdate
FROM dbo.Providers prov
RIGHT JOIN dbo.LagTime_Charges_Calcs lag ON prov.pgrp_prov_cd = lag.provcode
AND prov.pgrp_practice = lag.px
LEFT JOIN dbo.PlaceofService_union_v pos ON lag.px = pos.px
AND lag.poscode = pos.poscode
WHERE pos.posid='1' Or pos.posid ='2' AND prov.Laginclude ='y'
and MONTH(lag.postingdate) = 1
and YEAR(lag.postingdate) = 2015

--and lag.postingdate between '2015-01-01 00:00:00.000' and '2015-01-31 23:59:59.000'
--AND cast(convert(varchar(10),lag.postingdate,112) as datetime) between '2015- 01-01' and '2015-01-31'

Aucun commentaire:

Enregistrer un commentaire