DATEPART() Function | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

DATEPART() Function

I have two entries in a table in this example 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 Toni Chaffin
aka Toni
use Convert funtion to change your date value to correct format —————————————-

WHERE (src_date &gt;= convert(varchar(6),@dt_src_date,112)+ ’01’) AND (src_date &lt; DATEADD(month, 1, convert(varchar(6),@dt_src_date,112)+ ’01’) – 1)<br /><br />using dd/mm/yyyy without a style is dangerous as it depends on the connection. Check that the data is correctly represented when it runs.<br />It’s best to use yyyymmdd hh:mm<img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />s.mmm or yyyy-mm-ddThh:mm<img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />s.mmm<br />(the second one needs the T).<br /><br />The dates and variables are presumably held as datetimes in the database so you don’t have to worry about the format (dd/mm/yyyy) unless you are converting to character.<br /><br />
Thanks to you both for your assistance. I pasted the new WHERE staement in and now it works perfectly. All I have to do is understand what is has done so I never need ask this part again. Thank you Regards Toni Chaffin
aka Toni
You haven’t read yet the link I’ve mentioned here, right?<br /<a target="_blank" href=></a><br />[<img src=’/community/emoticons/emotion-5.gif’ alt=’;)‘ />]<br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=></a><br />Heute schon gebloggt?<a target="_blank" href=></a><br />Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=></a>)
I had forgotten that I posted that question. I have just read it now though and I understand what you are saying. Thanks
Toni Chaffin
aka Toni