Sql-comparing two date with system date

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

Sql-comparing two date with system date

Author

Topic

pradeep sweet
Starting Member

5 Posts

Posted - 2012-09-06 : 02:38:02

Hi,
This is my table
moviename movievalid_from movievalid_to
A 2012-09-11 2012:09:13
(datatype-datetime) (datatype-datetime)

B 2012-09-12 2012-09-12
(datatype-datetime) (datatype-datetime)

If the system date is 2012:09:11, then moviename “A” should come by using select query..
If the system date is 2012:09:12, then moviename”A and B” should come because 12 is in between 11-13 and in second row i have also given 12 by using select query..
If the system date is 2012:09:13, then moviename”A” should come by using select query…..
If the system date is 2012:09:14, then moviename should be empty by using select query………

I am using sql serever management studio express 2005 ,i need all possible result by one query,can any one help me?

chadmat
The Chadinator

1974 Posts

Posted - 2012-09-06 : 02:58:23

SELECT MovieName
FROM Table
WHERE myDate BETWEEN movievalid_from AND movievalid_to

-Chad

pradeep sweet
Starting Member

5 Posts

Posted - 2012-09-07 : 06:15:55

Hi Sir,
I tried your query but i am not getting the expected result,if the system date is 2012-09-12 i should get both the Moviename->A and B

quote:
Originally posted by chadmat

SELECT MovieName
FROM Table
WHERE myDate BETWEEN movievalid_from AND movievalid_to

-Chad

chadmat
The Chadinator

1974 Posts

Posted - 2012-09-07 : 12:53:19

You should. Is there a time on the valid to/from dates?

-Chad

pradeep sweet
Starting Member

5 Posts

Posted - 2012-09-08 : 09:47:02

hi Sir,
My table contains date 2012-09-11 00:00:00:00 in this format,if i use between operator means,i am not getting the exact result because when the system date is 2012:09:13 means i should get only moviename-"A" but if i use between operator means i am getting both "A" and "B" sir................

quote:
Originally posted by chadmat

You should. Is there a time on the valid to/from dates?

-Chad

chadmat
The Chadinator

1974 Posts

Posted - 2012-09-08 : 12:50:37

Based on your initial requirements, it works:

CREATE TABLE #Movies (moviename char(1), movievalid_from datetime, movievalid_to datetime)

INSERT INTO #Movies VALUES('A', '2012-09-11', '2012-09-13')
INSERT INTO #Movies VALUES('B', '2012-09-12', '2012-09-12')

SELECT MovieName
FROM #Movies
WHERE '2012-9-11' BETWEEN movievalid_from AND movievalid_to

SELECT MovieName
FROM #Movies
WHERE '2012-9-12' BETWEEN movievalid_from AND movievalid_to

SELECT MovieName
FROM #Movies
WHERE '2012-9-13' BETWEEN movievalid_from AND movievalid_to

SELECT MovieName
FROM #Movies
WHERE '2012-9-14' BETWEEN movievalid_from AND movievalid_to

DROP TABLE #Movies

Results:

MovieName
---------
A

MovieName
---------
A
B

MovieName
---------
A

MovieName
---------

So either I misunderstood your requirements, or you have left something out.

-Chad

pradeep sweet
Starting Member

5 Posts

Posted - 2012-09-13 : 00:27:57

Thank You Sir,
I got the exact result....Thanks a lot for valuable suggestion........

quote:
Originally posted by chadmat

Based on your initial requirements, it works:

CREATE TABLE #Movies (moviename char(1), movievalid_from datetime, movievalid_to datetime)

INSERT INTO #Movies VALUES('A', '2012-09-11', '2012-09-13')
INSERT INTO #Movies VALUES('B', '2012-09-12', '2012-09-12')

SELECT MovieName
FROM #Movies
WHERE '2012-9-11' BETWEEN movievalid_from AND movievalid_to

SELECT MovieName
FROM #Movies
WHERE '2012-9-12' BETWEEN movievalid_from AND movievalid_to

SELECT MovieName
FROM #Movies
WHERE '2012-9-13' BETWEEN movievalid_from AND movievalid_to

SELECT MovieName
FROM #Movies
WHERE '2012-9-14' BETWEEN movievalid_from AND movievalid_to

DROP TABLE #Movies

Results:

MovieName
---------
A

MovieName
---------
A
B

MovieName
---------
A

MovieName
---------

So either I misunderstood your requirements, or you have left something out.

-Chad

chadmat
The Chadinator

1974 Posts

Posted - 2012-09-13 : 02:23:05

You are welcome

-Chad

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-09-13 : 04:39:58

Also express datetime values in unambiguous format
http://beyondrelational.com/modules/2/blogs/70/posts/10898/understanding-datetime-column-part-ii.aspx

Madhivanan

Failing to plan is Planning to fail

pradeep sweet
Starting Member

5 Posts

Posted - 2012-09-14 : 03:05:31

Hello Sir,
This is pradeep again,i have started doing my next project in c#.net,it's a payroll project connected with biometric fingerprint device X990,i gave my fingerprint punch in that device at this date example(14-09-2012 12:26:56) for user ID=1,but i have found different result while retrieving it in front end the output is in like this format(1,1,2000 12:00:00)i am getting this output from device,i am in need of your help sir can u help me.......

quote:
Originally posted by chadmat

You are welcome

-Chad

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources