SQL Server Performance Forum – Threads Archive

Do I need DATEPART or similar?

I have a function that uses the following statement in it SELECT src_terrier.Areacode, src_terrier.siteref, src_terrier.estatename, src_terrier.Securitised, src_terrier.unitref, src_terrier.unittype,
src_terrier.unittype_count, src_terrier.tenantname, src_terrier.tenantstatus, src_terrier.tenantstatus_count, src_terrier.unitstatus,
src_terrier.unitstatus_count, src_terrier.floortotal, src_terrier.floortotocc, src_terrier.initialvacarea, src_terrier.initialvacnet, src_terrier.TotalRent,
src_terrier.NetRent, src_terrier.FinalRtLsincSC, src_terrier.ErvTot, src_terrier.tenancyterm, src_terrier.landact, src_terrier.datadate,
src_div_mgr.div_mgr, src_portfolio_mgr.portfolio_mgr, src_centre_list.propcat, src_tbl_rental.budgeted_net_rent,
src_tbl_rental.budgeted_occupancy
FROM src_terrier INNER JOIN
src_centre_list ON src_terrier.siteref = src_centre_list.Site_Ref AND src_terrier.Areacode = src_centre_list.Division INNER JOIN
src_div_mgr ON src_centre_list.Division = src_div_mgr.division INNER JOIN
src_portfolio_mgr ON src_centre_list.Portfolio_no = src_portfolio_mgr.portfolio_no LEFT OUTER JOIN
src_tbl_rental ON src_terrier.siteref = src_tbl_rental.site_ref WHERE (src_terrier.datadate = @dt_src_date) AND
(src_terrier.Areacode = @chr_div) AND
(src_centre_list.Portfolio_no = @vch_portfolio_no) AND
(src_centre_list.propcat = @vch_prop_cat) AND
(src_tbl_rental.site_ref = src_terrier.siteref) The problem I have is that the ‘src_terrier.datadate’ is passed through as mm/dd/yyyy (which I do actually want to change to dd/mm/yyyy as that is how the data is stored) however, the src_date within the table src_tbl_rental is only set to 01/mm/yyyy. When I put an inner join on the date element it obviously does not find it as the sample data I am using is as follows src_terrier = 28/04/2006 and src_tbl_rental is 01/04/2006. Therefore if I pass the same parameter value through the dates are not the same and I get no data at all. How can I specify that for the purposes of the src_tbl_rental element of the select query, that I only want it to match the mm/yyyy part of the src_date. Therefore if some passes in
28/04.2006 it will get the records from the terrier table that match that date, and only the records from rental that match the 04/2006 part of the date. Anybody confused by that , cause I am! Regards
Toni Chaffin
aka Toni

I assume you storing the value in a DATETIME column, right?
If so, forget about the "format" of the data. Internally it’s stored completely different. What you see is what your front-end turns the date value into. You might want to read this:http://www.sql-server-performance.com/fk_datetime.asp If you only need to change 28.04.2004 into 01.04.2006 for matching reasons, you can do
SELECT something
FROM sometable
JOIN some_other_table
ON something.src_tbl_rental = DATEADD(MONTH,DATEDIFF(MONTH,0,src_terrier),0) —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

Too quick… However, somehow I believe that JOINing on such a construct might not be the best choice. No other column(s) to JOIN on? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

Hi Yes the information in both tables is stored as datetime. There is only one other column to join on and that is already being used in the statement; …..LEFT OUTER JOIN
src_tbl_rental ON src_terrier.siteref = src_tbl_rental.site_ref
Toni Chaffin
aka Toni

Frank, not sure where did the "too quick" come from.
Toni, for the format, use what frank suggested or LEFT(CONVERT(VARCHAR, [datecolumn], 112),6) to BOTH side of the equation. You don’t have to worry about the format if it is a DATETIME data type. May the Almighty God bless us all!
www.empoweredinformation.com

Oh personally I think it’s better to JOIN on columns having the same data without the need to modify either side. But I don’t know this specific model or the whole purpose. Consider it just my $0.02 cents. [<img src=’/community/emoticons/emotion-1.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>)

Thank you to you both for your comments/suggestions. I will give them a try now. Question:- How can I set it so that when the user passes the value into the @parameter they do it in such a way that allows the user to enter 28.04.2006 (example). The user will not be manually typing the value, they will be selecting it from a drop down list within SQL 2005 Reporting Services. Regards Toni Toni Chaffin
aka Toni

I agree if the datatype is the same and data domain is the same. However, the data domain is not necessarily the same (ie. the "time" portion on the datetime). If you don’t have that issue, use directly the column else use the suggestion above. Your question, either you apply the format in the Selection control (combo box) with the format specified (or in SQL, style 104 of the convert, ie. CONVERT(VARCHAR, [columnselection], 104)).
May the Almighty God bless us all!
www.empoweredinformation.com

Are you sending this via an ADO command object with parameters to an SQL Server stored procedure or how do you call this statement? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

You need to send dates to sql server in universal format yyyy-mm-dd or yyyymmdd. So before sending it to report format it Madhivanan Failing to plan is Planning to fail

]]>