May 22, 2006 at 6:29 am
I have two entries in a table
site_ref src_date budget_rent budget_occ
AE 01/04/2006 123456.0000 69
AE 01/05/2006 371540.2432 74
When I run the following query
SELECT site_ref, budgeted_net_rent, budgeted_occupancy
FROM src_tbl_rental
WHERE (src_date >= @dt_src_date) AND (src_date < DATEADD(month, 1, @dt_src_date) - 1)
I get a result set that includes both of the above lines, when what I was trying to achieve was just the value for the first one only. I know that the date is entered in the database as 01/04/2006 as part of an import from excel. Not sure wther it is actually yyyy-mm-dd etc in reality, but from a visual point of view it certainly is dd/mm/yyyyy.
If I manually enter the (@dt_src_date) parameter of 01/04/2006 then it returns only the one line. The problem I have is that the parameter that I mentioned actually needs to provide dd/mm/yyyy data for the bulk of the other functions etc, I want this query to only return a value that will always the mm/yyyy element of the parameter value and prefixed by 01/.
Anybody help me out here?
Regards
May 22, 2006 at 6:40 am
Hope this works
SELECT site_ref, budgeted_net_rent, budgeted_occupancy
FROM src_tbl_rental
WHERE (convert(datetime,src_date,106) >= @dt_src_date) AND (convert(datetime,src_date,103) < DATEADD(month, 1, @dt_src_date) - 1)
May 22, 2006 at 6:42 am
I went with this as the option. Thanks for you suggestion though, much appreciated
WHERE (src_date >= CONVERT(varchar(6), @dt_src_date, 112) + '01') AND (src_date < DATEADD(month, 1, CONVERT(varchar(6), @dt_src_date, 112) + '01') - 1)
Regards
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply