RE: stored procedure compare dateTime

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Alejandro Mesa (AlejandroMesa_at_discussions.microsoft.com)
Date: 02/18/05 

Date: Fri, 18 Feb 2005 05:15:03 -0800

I recommend to pass datetime values with the format "yyyy-mm-ddThh:mm:ss.mmm"
(See CONVERT in BOL, style 126), in this way sql server will interpret it
correctly independently the language or date format settings.

...
, @MinDate = '2005-02-18T12:00:00', @MaxDate = '2005-08-07T12:00:00',
...

AMB

"Boonaap" wrote:

> yes, this is what is sent...
>
> spGetGuidedTours @CountryCode = N'USA', @RegionCode = N'%', @BudgetRate =
> N'€ 750 - 1000', @Accomodation = N'%', @Theme = N'%', @MinDate = 'Feb 18 2005
> 12:00AM', @MaxDate = 'Aug 7 2005 12:00AM', @minDays = 1, @maxDays = 26
>
> this is my SP, there has to be something wrong with it...
>
> IF EXISTS(SELECT name FROM sysobjects WHERE NAME = 'spGetGuidedTours')
> DROP PROC spGetGuidedTours
> GO
>
> CREATE PROC spGetGuidedTours
> (
> @CountryCode NVarChar ,
> @RegionCode NVarChar ,
> @BudgetRate NVarChar ,
> @Accomodation NVarChar ,
> @Theme NVarChar ,
> @MinDate smallDateTime ,
> @MaxDate smallDateTime ,
> @MinDays Int ,
> @MaxDays Int
> )
> as
>
> SELECT COUNT(*) AS Aantal
> FROM tblPriceRange INNER JOIN
> tblAccomodation INNER JOIN
> tblGuidedTourDescription INNER JOIN
> tblCountry ON tblGuidedTourDescription.COUNTRY = tblCountry.CountryID
> INNER JOIN
> tblGuidedTourInstances ON tblGuidedTourDescription.TOURID =
> tblGuidedTourInstances.TourIDFK ON
> tblAccomodation.AccomodationID = tblGuidedTourInstances.Accomodation ON
> tblPriceRange.RangePK = tblGuidedTourInstances.PRICERANGE INNER JOIN
> tblRegion ON tblGuidedTourDescription.REGION = tblRegion.RegionID AND
> tblCountry.CountryID = tblRegion.CountryIDFK INNER JOIN
> tblTheme ON tblGuidedTourDescription.THEME = tblTheme.ThemeID INNER JOIN
> tblTourProgram ON tblGuidedTourInstances.TourInstanceID =
> tblTourProgram.TourInstanceFK INNER JOIN
> tblCalendar ON tblTourProgram.dateTimeFK =
> tblCalendar.dateTimePK
>
>
> where tblCountry.CountryID like @CountryCode and
> tblRegion.RegionID like @RegionCode and
> tblPriceRange.PriceRange like @BudgetRate and
> tblAccomodation.AccomodationID like @Accomodation and
> tblTheme.ThemeID like @Theme and
> tblGuidedTourDescription.DAYS between @MinDays and @MaxDays and
> tblTourProgram.dateTimeFK >= @MinDate and tblTourProgram.dateTimeFK <
> @MaxDate
>
>
> SELECT distinct
> tblGuidedTourInstances.TourInstanceID, tblGuidedTourDescription.TourID,
> tblCountry.CountryID, tblCountry.CountryNL, tblCountry.CountryFR,
> tblCountry.CountryEN,
> tblRegion.RegionNameNL, tblRegion.RegionNameFR, tblRegion.RegionNameEN,
> tbltheme.themeID, tblTheme.ThemeDescriptionNL, tblTheme.ThemeDescriptionFR,
> tblTheme.ThemeDescriptionEN,
> tblGuidedTourDescription.TITLENL, tblGuidedTourDescription.TITLEFR,
> tblGuidedTourDescription.TITLEEN,
> tblGuidedTourDescription.NL, tblGuidedTourDescription.FR,
> tblGuidedTourDescription.EN,
> tblPriceRange.PriceRange, tblAccomodation.AccomodationDescription,
> tblGuidedTourDescription.DepartLocation,
> tblGuidedTourDescription.ArrivalLocation,
> tblGuidedTourDescription.DAYS
> FROM tblPriceRange INNER JOIN
> tblAccomodation INNER JOIN
> tblGuidedTourDescription INNER JOIN
> tblCountry ON tblGuidedTourDescription.COUNTRY = tblCountry.CountryID
> INNER JOIN
> tblGuidedTourInstances ON tblGuidedTourDescription.TOURID =
> tblGuidedTourInstances.TourIDFK ON
> tblAccomodation.AccomodationID = tblGuidedTourInstances.Accomodation ON
> tblPriceRange.RangePK = tblGuidedTourInstances.PRICERANGE INNER JOIN
> tblRegion ON tblGuidedTourDescription.REGION = tblRegion.RegionID AND
> tblCountry.CountryID = tblRegion.CountryIDFK INNER JOIN
> tblTheme ON tblGuidedTourDescription.THEME = tblTheme.ThemeID INNER JOIN
> tblTourProgram ON tblGuidedTourInstances.TourInstanceID =
> tblTourProgram.TourInstanceFK INNER JOIN
> tblCalendar ON tblTourProgram.dateTimeFK =
> tblCalendar.dateTimePK
> where tblCountry.CountryID like @CountryCode and
> tblRegion.RegionID like @RegionCode and
> tblPriceRange.PriceRange like @BudgetRate and
> tblAccomodation.AccomodationID like @Accomodation and
> tblTheme.ThemeID like @Theme and
> tblGuidedTourDescription.DAYS between @MinDays and @MaxDays and
> tblTourProgram.dateTimeFK >= @MinDate and tblTourProgram.dateTimeFK <
> @MaxDate
>
> When use the query with fixed data, i get a result
> select blablabla
> from blablalba
> where tblCountry.CountryID like 'USA' and
> tblRegion.RegionID like '%' and
> tblPriceRange.PriceRange like '€ 750 - 1000' and
> tblAccomodation.AccomodationID like '%' and
> tblTheme.ThemeID like '%' and
> tblGuidedTourDescription.DAYS between 1 and 26 and
> tblTourProgram.dateTimeFK >= 'Feb 18 2005 12:00AM' and
> tblTourProgram.dateTimeFK < 'Aug 7 2005 12:00AM'
>
>
> getting desperate here...
>
>
> "Alejandro Mesa" wrote:
>
> > Another thing, Did you try Profiler to see exactly what it is being sent to
> > sql server?
>

Relevant Pages

  • Re: WHERE clause applies to right-hand table of LEFT JOIN
    ... it's always best to specify INNER JOIN ... T1_PK int NOT NULL ... CONSTRAINT FK_T2_T1 FOREIGN KEY ... The SQL Server cost-based optimizer is very good at generating efficient ...
    (microsoft.public.sqlserver.server)
  • Re: WHERE clause applies to right-hand table of LEFT JOIN
    ... it's always best to specify INNER JOIN ... T1_PK int NOT NULL ... CONSTRAINT FK_T2_T1 FOREIGN KEY ... The SQL Server cost-based optimizer is very good at generating efficient ...
    (microsoft.public.access.queries)
  • Re: Stored Procedure CASE
    ... Columnist, SQL Server Professional ... Drew Laing ... > SQL Server MVP ... > C.CostCenterID INNER JOIN ...
    (microsoft.public.sqlserver.programming)
  • Re: "EXEC" in SQL Server 2000 Views
    ... Kalen Delaney ... > INNER JOIN b ... > Could not execute query against OLE DB provider 'MSDASQL'. ... >> SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: procedure tuning
    ... Pro SQL Server 2000 Database Design - ... >>> INNER JOIN tblVRM ... >>> INNER JOIN tblFDVSourceSurvey ... I am having problems re-writting a stored procedure to run quicker, ...
    (microsoft.public.sqlserver.programming)