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 —————————————-
http://dineshasanka.blogspot.com/

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) 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. 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 (the second one needs the T). 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.

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? http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=14404</a> [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a> Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</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

]]>