CREATE PROCEDURE with a date condition on inseret

Question

CREATE PROCEDURE with a date condition on inseret

midan1

SSCertifiable

Points: 5100
More actions
May 18, 2004 at 4:05 pm

#152150
need help to do this
CREATE PROCEDURE with a date condition on inseret

i wont to an insert in to table with condition on dateTIME
i have 4 fieds on the insert Page
1. Date_from = sort date
2. Date_end = sort date
3. Time_from = time
4. Time_end = time
and after the insert i show to the user only 2 fields
1. Date-from= Date_from +'-'+ Time_from
2. date-to = Date_end +'-'+ Time_end
so my question is how do this ?
If the date is BIG then One day (on the insert) then i get a value in a new field (name) "fld1"
fld1=1
and If the date is only One day Then i get a value in a new field (name) "fld1"
fld1=2

thnks ilan

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736568 More actions · Answer 1

Not sure what you mean. What's "BIG" and "One Day"? Can you provide some actual dates with examples? I suspect you want to use a CASE statement, just not sure how to structure it.

midan1 SSCertifiable Points: 5100 More actions · Answer 2

fix question

@Date_from = '31/06/2004'
@Date_end = '01/07/2004'
@Time_from = '07:00'
@Time_end = '08:00'
@fld1= " "

fld_date_from = @Date_from +'-'+ @Time_from

fld_date_to = @Date_end +'-'+ @Time_end

so my question is how do this ?

If the DATEDIFF BETWEEN fld_date_from and fld_date_to

is Big then 24 HOUR

then

fld1=1

If the DATEDIFF BETWEEN fld_date_from and fld_date_to

is small then 24 HOUR

then

fld1=2

how to to this ?

Antares686 SSC Guru Points: 125444 More actions · Answer 3

As long as you get both to resolve to a real datetime value (I sugest put into a datetime variable first) then you can use a case state. However due to rounding you might want to use minutes or seconds instead.

24 hr = 1440 min

24 hr = 86400 sec

So for seconds something like so

CASE WHEN DateDiff(s,Date1,Date2) > 86400 THEN 1

ELSE 2 END

midan1 SSCertifiable Points: 5100 More actions · Answer 4

how to do this

can i get any help o do this

thnks a lot ilan

midan1 SSCertifiable Points: 5100 More actions · Answer 5

How to do it ???

use CASE WHEN

and put it all in one ????

thnks a lot ilan

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 6

Hi Midan,

Guess there might be a little problem with the language barrier thing... let me see if I can clear it up...

I believe your problem is that you want to determine the difference between the two date/time pairs of columns and if the difference is more than 24 hours, you want to set Fld1 to "1". If the difference is less than or equal to 24 hours, you want to set Fld1 to "2".

The example below shows how to do this in a SELECT (for testing purposes) which you can modify to do your INSERT. I've included all of the variable declarations and "sets" just for testing purposes... the "meat" of this problem is in the CASE statement of the example. (by the way... June only has 30 days in it, not 31. )

--DECLARE things for testing purposes
DECLARE @Date_From VARCHAR(10) DECLARE @Date_End VARCHAR(10) DECLARE @Time_From CHAR(5) DECLARE @Time_Fnd CHAR(5)

--Setup the test values
SET @Date_From = '30/06/2004' SET @Date_End = '01/07/2004' SET @Time_From = '07:00' SET @Time_End = '08:00'

--Your "answer" to your problem is all in the CASE statement
SELECT CASE WHEN CONVERT(DATETIME,@Date_End,103)+CONVERT(DATETIME,@Time_End) -CONVERT(DATETIME,@Date_From,103)+CONVERT(DATETIME,@Time_From) > 1 THEN 1 ELSE 2 END AS Fld1

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)