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

yes, one way is using DatePart to compare the day,month and year separately.
Then you do not have to change the input format of mmddyyyy to ddmmyyyy.

Ex:

SET DATEFORMAT mdy
DECLARE @.Inputdate datetime
SET @.Inputdate = '04/28/2006'

SET DATEFORMAT dmy
DECLARE @.Tabledate1 datetime
SET @.Tabledate1 = '28/04/2006'

SET DATEFORMAT dmy
DECLARE @.Tabledate2 datetime
SET @.Tabledate2 = '01/04/2006'

if ( datepart(dd,@.Inputdate)= datepart(dd,@.Tabledate1) AND
datepart(mm,@.Inputdate)= datepart(mm,@.Tabledate1) AND
datepart(yyyy,@.Inputdate)= datepart(yyyy,@.Tabledate1) )
PRINT 'SAME DATES'
ELSE
PRINT 'DIFFERENT DATES'

If ( datepart(mm,@.Tabledate1)= datepart(mm,@.Tabledate2) AND datepart(yyyy,@.Tabledate1)= datepart(yyyy,@.Tabledate2) )
PRINT 'SAME MONTH AND YEAR'
ELSE
PRINT 'DIFF MONTH/YEAR'

GO

|||

The query search condition (WHERE clause) doesn't quite match what you are explaining. If you want to ignore the day part in the datetime value then you can use an expression like below:

select dateadd(day, 1 - day(CURRENT_TIMESTAMP), CURRENT_TIMESTAMP )

Please substitute CURRENT_TIMESTAMP with the date column or variable as required. Note that if you use functions or expressions on columns then the index if any on that column cannot be used to efficiently seek to the value. So performance of the query might suffer if your primary search/join condition is based on the date column. For example, you can modify search condition below:

(src_terrier.datadate = @.dt_src_date)

to:

(dateadd(day, 1 - day(src_terrier.datadate), src_terrier.datadate) = @.dt_src_date)

Assuming that @.dt_src_date is specified as YYYYMM01 then it will match fine. Another way to optimize the search is to do the following:

(src_terrier.datadate >= @.dt_src_date and

src_terrier.datadate < dateadd(month, 1, @.dt_src_date) - 1)

In this search condition, we use a lower and upper bound for the date values which is month start and month end. So it will match any date value in "datadate" column for the specific YYYYMM combination in @.dt_src_date.

|||

In addition to my previous question relating to datepart

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

|||

Problem Resolved by using

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