Passing DateDiff rather than Actual Dates as Parameters

[laurenq uantrell]

I've gotten sort of fed up with dealing with regional date settings on
the client side and am considering the following scheme - just
wondering if anyone has a negative view of it or not:

Instead of

@StartDate datetime,
@EndDate datetime

Use:

@StartDaysDiff int,
@EndDaysDiff int
In the front end app take the desired date and do a DATEDIFF with the
current date, then pass the date diff as a parameter as an integer
rather than deal with the dates at all.

Then...

DECLARE @TodayDate datetime, @StartDate datetime, @EndDate datetime

SELECT @TodayDate = GETDATE()
SELECT @StartDate = DATEDIFF(DAY,@StartDaysDiff,@TodayDate)
SELECT @EndDate = DATEDIFF(DAY,@EndDaysDiff,@TodayDate)

SELECT
a.Something
FROM
dbo.Appointments a
WHERE
a.AppointmentDate BETWEEN @StartDate and @EndDate

Just wondering ...

Thanks,
lq

[laurenq uantrell]

Oops,
I meant:

SELECT @TodayDate = GETDATE()
SELECT @StartDate = DATEADD(DAY,@StartDaysDiff,@TodayDate)
SELECT @EndDate = DATEADD(DAY,@EndDaysDiff,@TodayDate)

[Erland Sommarskog]

laurenq uantrell (la*************@hotmail.com) writes:

I've gotten sort of fed up with dealing with regional date settings on
the client side and am considering the following scheme - just
wondering if anyone has a negative view of it or not:

Instead of

@StartDate datetime,
@EndDate datetime

Use:

@StartDaysDiff int,
@EndDaysDiff int
In the front end app take the desired date and do a DATEDIFF with the
current date, then pass the date diff as a parameter as an integer
rather than deal with the dates at all.

You could run into nasty surprises if the client and server are in
different time zones. Then again, if you instead of using the current
date as your base date, took a fixed date such as 1900-01-01, you'd be safe.
Then again, this is also confusing as 1900-01-01 is the base date in SQL
Server while in Visual Basic it's 1899-12-30.

And I don't really see how this relieves you from dealing with regional
settings on the client side. After all, the user inputs a date, and the
interpretation of 1/7/9 is different depending on where you are. And
to transform that to a number, you still have to interpret it.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[laurenq uantrell]

On the client side, it's pretty simple since date() remains in the
chosen format as long as I never format it, so Nov.29, 2005 remains
29/11/2005 or 11/29/2005 depending on the regional format - the
DATEDIFF on the client side will always calculate correctly. What I
need to do is to translate the client date to UTC/GMT and pass that as
an integer to the server to handle the time zone differences as I have
shown below:

@GMTOffset int, /* client GMT offset in minutes - example NYC = 300
*/
@StartDays int, /* Number of days from today's date */
@EndDays int /* Number of days from today's date */

AS

DECLARE @ClientUTCDIFF int, @ServerUTCDIFF int, @ServerStartDate
datetime, @ServerEndDate datetime

/* determine the differnence between user client GMT offset and server
GMT Offset: */
SELECT @ServerUTCDIFF = DATEDIFF(MINUTE, GETDATE(), GETUTCDATE())
SELECT @ClientUTCDIFF = @GMTOffset - @ServerUTCDIFF

/* set server start date/time to GMT hour at midnight user's time today
- example NYC = 5am GMT*/
SELECT @ServerStartDate = DATEADD(MINUTE,@ServerUTCDIFF +
@ClientUTCDIFF,CAST(CONVERT(nvarchar(10),GETDATE() ,101) AS Datetime))

/* set server end date/time to GMT hour at 11:59 pm user's time today -
example NYC = 4:49 am GMT*/
SELECT @ServerEndDate = DATEADD(MINUTE,1439,@ServerStartDate)

/* adjust the server date/time to the filter date range requested by
the user: */
SELECT @ServerStartDate = DATEADD(DAY,@StartDays, @ServerStartDate)
SELECT @ServerEndDate = DATEADD(DAY,@EndDays,@ServerEndDate)

/* display the date parameters that will be used: */

SELECT
@ServerStartDate AS ServerStartDate,
@ServerEndDate AS ServerEndDate

[Terry Kreft]

Lauren,
I'll repeat the advice I gave you a couple of days ago in
comp.databases.ms-access
------------------------------------
The easiest way to handle passing dates to SQL Server is to pass it in one
of the formats which Convert understands, then immediately convert it to a
datetime variable.

So for instance you could pass a date in international format.

Format(Date, "YYYYMMDD")

and then in your SP

Create Procedure usp_some_proc
@indate varchar(10)
AS
Declare @real_date datetime

Select @real_date = Convert(datetime, @indate, 112)
-- Then you can just work with @real_date and not worry about the format
------------------------------------

--
Terry Kreft

"laurenq uantrell" <la*************@hotmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

I've gotten sort of fed up with dealing with regional date settings on
the client side and am considering the following scheme - just
wondering if anyone has a negative view of it or not:

Instead of

@StartDate datetime,
@EndDate datetime

Use:

@StartDaysDiff int,
@EndDaysDiff int
In the front end app take the desired date and do a DATEDIFF with the
current date, then pass the date diff as a parameter as an integer
rather than deal with the dates at all.

Then...

DECLARE @TodayDate datetime, @StartDate datetime, @EndDate datetime

SELECT @TodayDate = GETDATE()
SELECT @StartDate = DATEDIFF(DAY,@StartDaysDiff,@TodayDate)
SELECT @EndDate = DATEDIFF(DAY,@EndDaysDiff,@TodayDate)

SELECT
a.Something
FROM
dbo.Appointments a
WHERE
a.AppointmentDate BETWEEN @StartDate and @EndDate

Just wondering ...

Thanks,
lq

[Erland Sommarskog]

laurenq uantrell (la*************@hotmail.com) writes:

On the client side, it's pretty simple since date() remains in the
chosen format as long as I never format it, so Nov.29, 2005 remains
29/11/2005 or 11/29/2005 depending on the regional format - the
DATEDIFF on the client side will always calculate correctly. What I
need to do is to translate the client date to UTC/GMT and pass that as
an integer to the server to handle the time zone differences as I have
shown below:

Or just pass it as a parameter to your queries, and the client API
will convert the date to a binary format. (Which accidently is based
on the number of days since 1900-01-01.)

Your diff stuff sounds overly complex to me.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[laurenq uantrell]

Erland,
Why pass it as a string and then convert it to a date? In your example
you're passing the formatted date (Format(Date, "YYYYMMDD") ) as a
string to the varchar parameter, then converting it to a date in
@real_date? Why not just pass it to a datetime parameter in the first
place and have the error reporting of the client deal with formatting
problems rather than the vague-aries of SQL error reporting?

@indate varchar(10)
AS
Declare @real_date datetime
Select @real_date = Convert(datetime, @indate, 112)

[laurenq uantrell]

Maybe you're right but it's pretty bullet proof and passing integers
seems preferable to passing strings. Plus, I'm always dealing with date
and time as opposed to date when dealing with clients in multiple time
zones, so since everthing is converted to UTC anyway, I figure why not
just look at dealing with DATEDIFF as opposed to date parameters. I
just throw this out as a theoretical since I am already heavily
invested in datetime parameters in my current projects.
I imagine this would have implications on query performance as well
since the stored procedures would be using DECLARE'd variables as WHERE
filters (WHERE StartDate BETWEEN @ServerStartDate and @ServerEndDate...)

[Erland Sommarskog]

laurenq uantrell (la*************@hotmail.com) writes:

Erland,
Why pass it as a string and then convert it to a date? In your example
you're passing the formatted date (Format(Date, "YYYYMMDD") ) as a
string to the varchar parameter, then converting it to a date in
@real_date? Why not just pass it to a datetime parameter in the first
place and have the error reporting of the client deal with formatting
problems rather than the vague-aries of SQL error reporting?

@indate varchar(10)
AS
Declare @real_date datetime
Select @real_date = Convert(datetime, @indate, 112)

Well, it wasn't me who suggested that, and I entirely agree with you.
Passing datetime values as strings is a poor idea.


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

laurenq uantrell (la*************@hotmail.com) writes:

Maybe you're right but it's pretty bullet proof and passing integers
seems preferable to passing strings.
And binary values are even better. You should not pass dates as strings
to SQL Server. You should pass them as datetime values.
I imagine this would have implications on query performance as well
since the stored procedures would be using DECLARE'd variables as WHERE
filters (WHERE StartDate BETWEEN @ServerStartDate and @ServerEndDate...)

Yes, if these are local variables and not parameters, SQL Server cannot
do parameter sniffing on them, but will apply standard guesses.

One way to circumvent this is to have a wrapper SP which unpacks the
integer values, and then call an inner SP which have datetime parameters.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx