Parameters Date Time

[codrutza]

Microsoft SQL Server Management Studio Express

…
ALTER PROCEDURE [dbo].[testc]
@FromDate smalldatetime,
@ToDate smalldatetime

AS

SELECT
jobs
….
FROM
….

WHERE
….
(@FromDate<=t.Date and t.Date<=@ToDate)

I have a report with Crystal Reports XI, based on the procedure above.

When run from Crystal Reports, the report asks for parameters with the time 00:00:00, and retrieves correctly the jobs.

When I attach the report to a system and we run it, the report asks for parameters datetime, with default actual time. We have to change the time, 00:00:00, in order to obtain correct results. As a job that is after a time eg 14:40:50 does not appear. My question could be silly, but why we have to change the time? It shouldn’t matter what time it is, because the jobs in the system have date only.

Please help.

 

[gmmastros]

I assume t.Date is a small date time data type. If so, that data type always contains time even if it is midnight.

Assuming @FromDate and @ToDate are actually different days... the problem would be with your @FromDate. Specifically this.... if something happened today, you would enter '2014-06-10' in the table. It would be stored as '2014-06-10 12:00:00' Then later, if you query for t.Date >= '2014-06-10 10:00 AM', the row would not be returned because 0:00 is less than 10:100 AM.

If you are using SQL2008 or newer, there is a Date data type that you could use. Date data does not store time so these problem would likely go away, although you would need to test this thoroughly as well as any other stored procedures that use the table.

Alternatively, you could remove the time part from your parameters. Specifically... leave the date portion and set the time portion to midnight.

ALTER PROCEDURE [dbo].[testc] 
@FromDate smalldatetime,
@ToDate smalldatetime

AS

[code]
Set @FromDate = DateAdd(Day, DateDiff(Day, 0, @FromDate), 0)
Set @ToDate   = DateAdd(Day, DateDiff(Day, 0, @ToDate), 0)

SELECT
jobs
….
FROM
….

WHERE
….
(@FromDate<=t.Date and t.Date<=@ToDate)
[/code]

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I screwed up the code block...

ALTER PROCEDURE [dbo].[testc] 
@FromDate smalldatetime,
@ToDate smalldatetime

AS

[!]
Set @FromDate = DateAdd(Day, DateDiff(Day, 0, @FromDate), 0)
Set @ToDate   = DateAdd(Day, DateDiff(Day, 0, @ToDate), 0)
[/!]

SELECT 
jobs
….
FROM 
….

WHERE 
….
(@FromDate<=t.Date and t.Date<=@ToDate)

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[codrutza]

Thank you for your reply.
I don’t have the Date data type.
I tried to do what you said and it seemed to work. But still, the time appear in the parameters when we run the report, confusing the users. Or I didn’t understand right?

 

[gk53]

to suppress time in report use convert(varchar(12), t.date,101) in selected fields it will remove time from report

 

[codrutza]

I'm sorry, I can't understand where I have to put convert(varchar(12), t.date,101)

 

[gk53]

if you have something like
select t.date from yourtable t where your condition
and output look like
2014-05-05 12:00:00.0
----------------------
you can use
select convert(varchar(12),t.date,101) as dt from yourtable t where your condition
and it will give you
05/05/2014

 

[codrutza]

Thank you for reply. I can use your formula for converting dates, but I still can't understand how to convert the parameters. I have for example Set @FromDate = convert(varchar(12),DateAdd(Day, DateDiff(Day, 0, @FromDate), 0),101)
Set @ToDate = convert(varchar(12),DateAdd(Day, DateDiff(Day, 0, @ToDate), 0),101)
but the report it's still asking me the parameters datetime, because I don't know how to define them otherwise.

 

[gk53]

did you tried just change you where condition from
(@FromDate<=t.Date and t.Date<=@ToDate

to
where t.Date between @FromDate and @ToDate
?

 

[gk53]

or even better
where t.Date between convert(varchar(12),@FromDate,101) and convert(varchar(12),@ToDate,101)

 

[codrutza]

I'm sorry, but I can't understand what type to define the parameters anymore

ALTER PROCEDURE [dbo].[Z_Test]

@FromDate ????
@ToDate ?????

SELECT …

FROM…

WHERE …

AND
t.Date between convert(varchar(12),@FromDate,101) and convert(varchar(12),@ToDate,101)

 

[gk53]

you can have
@FromDate date,
@ToDate date
and use
t.Date between convert(varchar(12),@FromDate,101) and convert(varchar(12),@ToDate,101)

or just
ALTER PROCEDURE [dbo].[Z_Test]
@FromDate varchar(10),
@ToDate varchar(10)
as

and
t.Date between@FromDate and @ToDate

whatever you like...

 

[codrutza]

- I don’t have the type date
- When I write
ALTER PROCEDURE [dbo].[Z_Test]
@FromDate varchar(10),
@ToDate varchar(10)
as

and
t.Date between@FromDate and @ToDate

And I run the report inputting the parameters 01/05/2014 and 10/05/2014 retrieves all dates (june)

This doesn’t make sense for me, with t.Date between@FromDate and @ToDate I want to compare date with string???

 

[gk53]

as long as you passing in varchar parameter date query will work
really you want to execute query like
select * from table where myDate between '1/1/2014' and '4/1/2014'

your sql statement just a string...
so if @startdate = '1/1/2014' and @endDate = '4/1/2014'
select * from table where myDate between @startdate and @endDate

will produce the same query string...

 

[gmmastros]

This doesn’t make sense for me, with t.Date between@FromDate and @ToDate I want to compare date with string???

The query will run as long as @FromDate and @ToDate represent valid dates. When you compare data of differing data types, SQL Server will do an implicit data type conversion. There are well defined rules for how the conversion is done. In this case, SQL Server will recognize that you are comparing strings with dates. Based on "SQL Server Data Type Precedence", the strings will be converted to date (and not the other way around).

Please be aware that if you use varchar for your parameters, you should validate the data prior to using it because the user could then type in anything they want. For example, they could type in "Today", which is not a valid date.

Additionally, you should be aware of potential problems with date formats. Specifically, is 1/2/2014 Jan 2 or Feb 1?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom