Parameters Date instead DateTime 1

[codrutza]

thread183-1661264

SQL Server Mg Studio Express

I don't have the date function.
I'm trying to convert in string but I don't know how to write it in the SP.

Please help me with this:

USE [DB]
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[Z_TEST]
@FromRepDate smalldatetime,
@ToRepDate smalldatetime

AS

DECLARE @DateStart VARCHAR(8)
DECLARE @DateEnd VARCHAR(8)

SET @DateStart = CONVERT(VARCHAR(8), @FromRepDate, 3) AS [DD/MM/YY]

SET @DateEnd = CONVERT(VARCHAR(8), @ToRepDate, 3) AS [DD/MM/YY]

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT
…..

FROM
….

WHERE
…
AND (FreightJobs.RepDate >= @DateStart AND FreightJobs.RepDate<@DateEnd)
…

Gives me the error
Incorrect syntax near the keyword 'AS'.

 

[imex]

What version of SQL Server are you using? 2000, 2005, 2008, 2012?

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[codrutza]

2005

 

[bborissov]

Just remove time portion from parameters:

ALTER PROCEDURE [dbo].[Z_TEST]
@FromRepDate smalldatetime,
@ToRepDate smalldatetime

AS
SET @FromRepDate = DATEADD(dd,DATEDIFF(dd,0, @FromRepDate), 0)
SET @ToRepDate   = DATEADD(dd,DATEDIFF(dd,0, @ToRepDate  ), 0)

...
WHERE
…
AND (FreightJobs.RepDate >= @FromRepDate AND FreightJobs.RepDate<@ToRepDate)

Borislav Borissov
VFP9 SP2, SQL Server

 

[codrutza]

Thank you for your replies.

It still gives me datetime, Borislav.

I’m trying to explain what I was trying to do.
I base on the SP a report made with CrystalReports.

When I run the report, appears a calendar at each parameter @FromDepDate, @ToDepDate, including the time(minutes, seconds...) and I want to appear just the date (Or, I have the option "Please enter DateTime in format "yyyy-mm-dd hh:mm:ss"" and I want to appear "Please enter Date ..." )

I want to covert from smalldatetime in smalldate or something like that.

simian336 suggested me that I could convert it to a string and take the first 10 characters and this is what I was trying to do.
Any idea, please?

 

[bborissov]

Pass parameters as you wish, make them string in CrystalReport.
Leave it as datetime in SP.
SQL Server is smart enough to convert them from string to datetime.
So, let user enter date (not datetime), pass it as parameter to sql server or convert it to varchar() and then pass it.

Something like this:

ALTER PROCEDURE [dbo].[Z_TEST]
      @FromRepDate smalldatetime,
      @ToRepDate smalldatetime
AS
  BEGIN
      SET NOCOUNT ON;
      SELECT @FromRepDate, @ToRepDate
  END

GO

EXEC Z_TEST '20131126', '20131129'
GO

As you see I pass parameters as varchar(8)m but the resutset returned from SP is DateTime.


Borislav Borissov
VFP9 SP2, SQL Server

 

[codrutza]

The parameters come to Crystal Reports as they are defined in SP and I can’t modify the type of them in Crystal Reports, because the box with the parameter type is inactive when the report is based on a SP.

I can change the format in date only, but when I run (the users run) the report, the report still ask to put the parameters in datetime:
Please enter DateTime in format "dd/mm/yyyy hh:mm:ss".

Users find it confusing, because I’ve done reports with Crystal Reports based on tables and we didn’t have this problem, but I am trying to remake the reports on SP, because of performance and security.

 

[imex]

The 2005 version does not have a data type for date without the time:
[link]

http://technet.microsoft.com/en-us/library/ms187819(v=sql.90).aspx

[/url]

Maybe you can try something this:

ALTER PROCEDURE [dbo].[Z_TEST] 
@FromRepDate VARCHAR(10),   -- dd/mm/yyyy
@ToRepDate VARCHAR(10)      -- dd/mm/yyyy

AS

DECLARE @DateStart DATETIME;
DECLARE @DateEnd DATETIME;

SET @DateStart = CONVERT(DATETIME, @FromRepDate, 103);
SET @DateEnd = CONVERT(DATETIME, @ToRepDate, 103);

SELECT -- ...
WHERE FreightJobs.RepDate BETWEEN @DateStart AND @DateEnd

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[codrutza]

Thanks, Imex. I've tried your suggestion and it works and I learnt something new as well.
Another question please: can I have ddmmyyyy or ddmmyy instead of dd/mm/yyyy? I couldn't find other code instead 103 to do that.

 

[imex]

Try:

-- @FromRepDate = ddmmyyyy
SET @DateStart = CONVERT(DATETIME, RIGHT(@FromRepDate, 4) + SUBSTRING(@FromRepDate, 3, 2) + LEFT(@FromRepDate, 2), 112);

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[codrutza]

Thanks, imex. It works.