Do I need DATEPART or similar

Question

Do I need DATEPART or similar

Toni Chaffin

SSCommitted

Points: 1513
More actions
May 11, 2006 at 5:45 am

#172556

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
Tonic

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

SQLBill SSC Guru Points: 51440 More actions · Answer 1

DATETIMEs are NOT stored in any 'date' recognizable format. They are not stored as 1/20/2006, 20/1/2006, or 2006-01-20. They are stored as bits. It is the front end that translates those bits to a recognizable format.

How are your dates stored? Are they DATETIME datatypes or VARCHAR (CHAR, NVARCHAR, NCHAR)?

If they are stored as a string (non DATETIME datatype), then you have to convert them to DATETIME. That will be tough if you have two different formats stored in your database.

-SQLBill

Toni Chaffin SSCommitted Points: 1513 More actions · Answer 2

They are stored as DateTime in both tables

Regards

Pam Brisjar SSChampion Points: 12094 More actions · Answer 3

If they are both indeed datetime, then datepart should work.

WHERE (DATEPART(mm,src_terrier.datadate) = DATEPART(mm,@dt_src_date) AND

DATEPART(yyyy,src_terrier.datadate) = DATEPART(yyyy,@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)

See:

http://msdn2.microsoft.com/en-us/library/ms174420.aspx

Toni Chaffin SSCommitted Points: 1513 More actions · Answer 4

Fantastic, thank you for your detailed post. I shall apply that with immediate effect and see how I go.

Your effort is much appreciated.

Regards

Pam Brisjar SSChampion Points: 12094 More actions · Answer 5

You're welcome.

Let us know if you have any further questions / problems.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

Just an alternative to Pam's good code...

WHERE DATEDIFF(mm,0,src_terrier.datadate) = DATEDIFF(mm,0,@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)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Toni Chaffin SSCommitted Points: 1513 More actions · Answer 7

Hi Pam/Jeff

I have used Pam's suggested code in my function now and I think it works. Certainly data is being returned. However what I do not understand is how it works. In the suggestions made I can see no reference to the rental date apart from (src_tbl_rental.site_ref = src_terrier.siteref) so how is it telling the query that I want to look at the full date of (src_terrier.datadate = @dt_src_date) and only the month and year of src_tbl_rental.src_date.

I am not knocking what has been suggested, I am just trying to learn the logic behind what is suggested here. I will have multiple possibilities of src_terrier.datadate and whatever the value is, I only want it to look at the mm/yyyy of the src_terrier.datadate when it is looking for the comparison in src_tbl_rental.src_date.

Kindest Regards

Conway Painting Right there with Babe Points: 785 More actions · Answer 8

Hi,

I think you'll find that the WHERE clause is referencing only the month and year portions of the date in the src_terrier table

WHERE (DATEPART(mm,src_terrier.datadate) = DATEPART(mm,@dt_src_date) AND DATEPART(yyyy,src_terrier.datadate) = DATEPART(yyyy,@dt_src_date))

But the SELECT clause is asking for the Src_Terrier.datadate, which is the complete date not just a portion of it. The link between the two tables is not dependent on the date, it uses a value "SiteRef".

Incidentally, to get the DMY format you want from a stored procedure, You can use

Set Dateformat MDY

At the top of SP.

Have fun

Cp

Toni Chaffin SSCommitted Points: 1513 More actions · Answer 9

Are OK, yeah that makes sense. Thank you for that peice of information

Regards

Toni Chaffin SSCommitted Points: 1513 More actions · Answer 10

Not sure what is going on here but it is now returning src_rental info for dates that do not exist within it

The data in the src_terrier table now contains data with the datadate of 28/04/2006, 05/05/2006 and 12/05/2006. Rental only contain upto and including 01/03/2006.

I do not understand how it is picking up any rental information as the mm/yyyy does not appear rental table as provided by the code above?

Regards

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 11

Beat me to it, Conway... absolutely correct.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 12

Hi Toni,

Could you post the current code you are using that is coughing up the aborant data, please?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Toni Chaffin SSCommitted Points: 1513 More actions · Answer 13

Hi Jeff

Please find the code as requested

ALTER

FUNCTION [dbo].[fnWTRalldata]

(

@dt_src_date

datetime,

@chr_div

char(2),

@vch_portfolio_no

tinyint,

@vch_prop_cat

nvarchar(4)

)

RETURNS

@WeeklyTerrierRSPI

TABLE

(

Areacode varchar(2),

siteref

nvarchar(3),

estatename

nvarchar(100),

Securitised

nvarchar(255),

unitref

nvarchar(15),

unittype

nvarchar(30),

unittype_count

int,

tenantname

nvarchar(100),

tenantstatus

nvarchar(25),

tenantstatus_count

int,

unitstatus

nvarchar(15),

unitstatus_count

int,

floortotal

float,

floortotocc

float,

initialvacarea

float,

initialvacnet

float,

TotalRent

float,

NetRent

float,

FinalRtLsincSC

float,

ErvTot

float,

tenancyterm

datetime,

landact

nvarchar(255),

datadate

datetime,

div_mgr

varchar(50),

portfolio_mgr

varchar(50),

propcat

nvarchar (4),

budgeted_net_rent

money,

budgeted_occupancy

decimal(18,0))

AS

BEGIN

INSERT @WeeklyTerrierRSPI

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

(DATEPART(mm,src_terrier.datadate) = DATEPART(mm,@dt_src_date) AND

DATEPART(yyyy,src_terrier.datadate) = DATEPART(yyyy,@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)

RETURN

END

Thank you Jeff

Regards

vadba SSChampion Points: 11133 More actions · Answer 14

Why do you use a LEFT OUTER JOIN here:

LEFT OUTER JOIN src_tbl_rental ON src_terrier.siteref = src_tbl_rental.site_ref

Obviously, since you are joining 5 tables about which I know nothing, I can't fully analyze the query, but if you are getting out-of-range data, the OUTER JOIN is the first place to look.