DATE PARAMETER - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

DATE PARAMETER

Author

Topic

divan
Posting Yak Master

153 Posts

Posted - 2012-08-28 : 12:34:25

I AM WRITING A SCRIPT THAT IS CREATING ONE #TEMP1 FILE BY USING THE "UNION" COMMAND. I AM GOING TO INCORPORATE THE SCRIPT INTO CRYSTAL REPORT. THE ISSUE I AM HAVING IS THE EXTRACT HAS TO DIFFERENT @HIGH_DATE PARAMETERS. WHAT I NEED IS THAT WHEN I RUN THE CRYSTAL REPORT AND GIVE IT THE TWO DATES FOR THE SCRIPT TO CALCULATE THE APPROPRIATE DATES...

EXAMPLE
SO WHEN I RUN THE CRYSTAL REPORT I WILL RUN IT AS

EXECUTE TEMP_1 '1/1/12','7/31/12'

CREATE PROC TEMP_1
DECLARE @LOW_DATE DATETIME,@HIGH_DATE DATETIME

SELECT X,Y AND Z
WHERE DATE BETWEEN @LOW_DATE AND @HIGH_DATE (in this instance I need the @HIGH_DATE to 7/1/12)

UNION
SELECT ABC
WHERE TERM_DATE <= @HIGH_DATE (in this instance I need the date to be 7/31/12)

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-28 : 13:09:03

maybe

CREATE PROC TEMP_1
@LOW_DATE DATETIME,
@HIGH_DATE DATETIME
as

SELECT X,Y,Z
from mytbl
WHERE DATE BETWEEN @LOW_DATE AND @HIGH_DATE
or TERM_DATE <= @HIGH_DATE
go

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

divan
Posting Yak Master

153 Posts

Posted - 2012-08-28 : 14:11:04

I THINK YOU MISSED THE PART IN THE () WHERE I HAVE MENTIONED WHAT I NEED THE @HIGH_DATE TO BE AT THE TWO INSTANCES...

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-08-28 : 14:20:48

Please typepost in mixed-case, it is much more difficult to read all caps. That having been said, here is a simpel way to get the first of the month from a date:

DECLARE @HIGH_DATE DATETIME = '2012-07-31';

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @HIGH_DATE), 0)

divan
Posting Yak Master

153 Posts

Posted - 2012-08-28 : 14:29:05

I apologize for the having the type post in all caps... the second solution does not solve the issue..

I need the solution for a case below..

EXAMPLE
When I run a Crystal report

I will EXECUTE TEMP_1 '1/1/12','7/31/12'

CREATE PROC TEMP_1
DECLARE @LOW_DATE DATETIME,@HIGH_DATE DATETIME

SELECT X,Y AND Z
WHERE DATE BETWEEN @LOW_DATE AND @HIGH_DATE (in this instance I need the @HIGH_DATE to 7/1/12)

UNION
SELECT ABC
WHERE TERM_DATE <= @HIGH_DATE (in this instance I need the date to be 7/31/12)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-08-28 : 14:36:38

I only provided one solution since you showed the parameter as being 7/31/12, which is the same as the desired predicate in your second query. Are you saying that the parameter could contain an abitrary date and it may not be the last day of the month?

Is your question: I have a DATETIME parameter and I need to use that parameter in two queries. In the first query I need to convert that datetime to the same month and year, but the first day of the month. For the second query I need to convert that parameter to last day of the month, preserving the month and year?

EDIT: Forgot code..
Assuming that is true, maybe this will help:

DECLARE @HIGH_DATE DATETIME = '2012-07-16';

SELECT 
    DATEADD(MONTH, DATEDIFF(MONTH, 0, @HIGH_DATE), 0) AS StartOfMonth,
    DATEADD(DAY, - 1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @HIGH_DATE) + 1, 0)) AS EndOfMonth

divan
Posting Yak Master

153 Posts

Posted - 2012-08-28 : 14:49:07

Perfect!!!!

Thank you very much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources