Check The DateTime - 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

Check The DateTime

Author

Topic

vijaynetpart
Starting Member

18 Posts

Posted - 2009-03-23 : 01:22:48

I m having the below 4 columns in my table...

StartDate StartTime EndDate EndTime
------------------------------------------------------ -------------------- ------------------------------------------------------
2009-03-11 00:00:00.000 10:10 AM 2009-03-13 00:00:00.000 12:10 PM
2009-03-19 00:00:00.000 10:10 AM 2009-03-19 00:00:00.000 12:10 PM
2009-03-05 00:00:00.000 10:00 AM 2009-03-05 00:00:00.000 03:00 AM
2009-03-08 00:00:00.000 10:00 AM 2009-03-08 00:00:00.000 03:00 AM
2009-03-11 00:00:00.000 10:00 AM 2009-03-11 00:00:00.000 03:00 AM

I m passing the below 4 arguments..
@StartDate = '2009-03-12'
@StartTime = '12:15 PM'
@EndDate = '2009-03-12'
@EndTime = '02:45 PM'

I like to check the between time are exist in the table or not....

How can i check this?

matty
Posting Yak Master

161 Posts

Posted - 2009-03-23 : 01:35:54

What is the expected output in this case?

quote:
Originally posted by vijaynetpart

I m having the below 4 columns in my table...

StartDate StartTime EndDate EndTime
------------------------------------------------------ -------------------- ------------------------------------------------------
2009-03-11 00:00:00.000 10:10 AM 2009-03-13 00:00:00.000 12:10 PM
2009-03-19 00:00:00.000 10:10 AM 2009-03-19 00:00:00.000 12:10 PM
2009-03-05 00:00:00.000 10:00 AM 2009-03-05 00:00:00.000 03:00 AM
2009-03-08 00:00:00.000 10:00 AM 2009-03-08 00:00:00.000 03:00 AM
2009-03-11 00:00:00.000 10:00 AM 2009-03-11 00:00:00.000 03:00 AM

I m passing the below 4 arguments..
@StartDate = '2009-03-12'
@StartTime = '12:15 PM'
@EndDate = '2009-03-12'
@EndTime = '02:45 PM'

I like to check the between time are exist in the table or not....

How can i check this?

vijaynetpart
Starting Member

18 Posts

Posted - 2009-03-23 : 01:51:03

Any one result like the bit or some string...

If exist -- return 1
Else --- return 0

vijaynetpart
Starting Member

18 Posts

Posted - 2009-03-23 : 09:21:58

plz anyone help me?

Siji
Starting Member

4 Posts

Posted - 2009-03-24 : 05:05:12

Hope that the parameters you are passing are of datetime format and the table columns are also of datetime format.

SET @StartTime = @StartDate + @StartTime
SET @EndTime = @EndDate + @EndTime

IF EXISTS
(SELECT StartDate FROM <TABLENAME>
WHERE
@StartTime BETWEEN startdate + starttime AND enddate + endtime
OR @EndTime BETWEEN startdate + starttime AND enddate + endtime)
BEGIN
SELECT 1
END
ELSE
BEGIN
SELECT 0
END

If the column datatype is not datetime, you need to cast it to datetime

vijaynetpart
Starting Member

18 Posts

Posted - 2009-03-24 : 05:31:57

quote:
Originally posted by Siji

Hope that the parameters you are passing are of datetime format and the table columns are also of datetime format.

SET @StartTime = @StartDate + @StartTime
SET @EndTime = @EndDate + @EndTime

IF EXISTS
(SELECT StartDate FROM <TABLENAME>
WHERE
@StartTime BETWEEN startdate + starttime AND enddate + endtime
OR @EndTime BETWEEN startdate + starttime AND enddate + endtime)
BEGIN
SELECT 1
END
ELSE
BEGIN
SELECT 0
END

If the column datatype is not datetime, you need to cast it to datetime

In this u have check only the @StartTime,@EndTime......
But my question is check the between times also (@StartTime<->@EndTime)

Siji
Starting Member

4 Posts

Posted - 2009-03-24 : 06:34:57

If your input period should be within the table's period, replace the 'OR' with 'AND'.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources