Select statement with DateTime

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

SQL Server 2000 Forums

SQL Server Development (2000)

Select statement with DateTime

Author

Topic

MuraliArun
Starting Member

3 Posts

Posted - 2007-07-04 : 03:14:29

Hi all,
I have doubt in Selecting records from the Table is as

tblNameDetails

ID int
Name varchar(100)
stdate datetime
endate datetime

Now i need to retrive Name from the table by following condtion

for ex [if the Start Date is 08/01/07 and the End Date is 09/01/07 the records should be selected from the table on 08/01/07 at 12:00:01AM and should not be selected on 09/01/07 at 11:59:59 PM]Please post ur thoughts/answers.....

Murali A

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-04 : 03:17:12

Select * from YourTable Where StDate >= '20070801' and EndDate <= '20070901' --YYYYMMDD format

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

MuraliArun
Starting Member

3 Posts

Posted - 2007-07-04 : 04:06:15

dinakar,
Thanks for ur reply .. but i need to restrict the records with Time also

sathiya
Starting Member

4 Posts

Posted - 2007-07-04 : 04:44:54

pl use this as where condition and try
stdate>= '2007-01-07 12:00:01 AM' and endate <= '2007-01-08 11:59:59 PM'

quote:
Originally posted by MuraliArun

Hi all,
I have doubt in Selecting records from the Table is as

tblNameDetails

ID int
Name varchar(100)
stdate datetime
endate datetime

Now i need to retrive Name from the table by following condtion

for ex [if the Start Date is 08/01/07 and the End Date is 09/01/07 the records should be selected from the table on 08/01/07 at 12:00:01AM and should not be selected on 09/01/07 at 11:59:59 PM]Please post ur thoughts/answers.....

Murali A

MuraliArun
Starting Member

3 Posts

Posted - 2007-07-04 : 04:54:04

stdate '2007-01-07 12:00:01 AM' and endate <= '2007-01-08 11:59:59 PM' is not the hardcoded date it will be GETDATE() ( i need to check with current date) function

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-04 : 10:01:32

[code] StDate > dateadd(day, datediff(day, 0, getdate()), 0)
and EnDate < dateadd(day, datediff(day, 0, getdate()), 2)[/code]

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-04 : 10:22:52

http://sql-server-performance.com/fk_datetime.asp

Madhivanan

Failing to plan is Planning to fail

DeepakNewton
Starting Member

39 Posts

Posted - 2007-07-11 : 02:04:54

khtan,
thanks for ur reply but still need to tune....

if the Start Date is (07/15/07 and the End Date is 07/16/07 the records should be selected from the table on 07/15/07 at 12:00:01AM and should not be selected on 09/16/07 at 11:59:59 PM

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-11 : 02:10:32

quote:
Originally posted by DeepakNewton

khtan,
thanks for ur reply but still need to tune....

if the Start Date is (07/15/07 and the End Date is 07/16/07 the records should be selected from the table on 07/15/07 at 12:00:01AM and should not be selected on 09/16/07 at 11:59:59 PM

You only want records falls between
2007-07-15 00:00:00 and 2007-07-16 23:59:59
right ?

Did you try my query at all ?

KH
[spoiler]Time is always against us[/spoiler]

DeepakNewton
Starting Member

39 Posts

Posted - 2007-07-11 : 04:28:36

khtan

Its like i need to check the table with current date (2007-07-11 12:00:01 AM) if the records was there in the table like stdate > 2007-07-11 12:00:01 AM means i need to pull the data.

At the same time if end date is > current date or getdate 2007-07-11 23:59:59 then no need to pull the record

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-11 : 04:35:13

quote:
Originally posted by DeepakNewton

khtan

Its like i need to check the table with current date (2007-07-11 12:00:01 AM) if the records was there in the table like stdate > 2007-07-11 12:00:01 AM means i need to pull the data.

At the same time if end date is > current date or getdate 2007-07-11 23:59:59 then no need to pull the record

WHERE StDate > DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
AND   EnDate < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 1)

Note :
DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) will gives you today's date at 00:00
DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 1) will gives you tomorrows' date at 00:00

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-11 : 04:36:12

Just realized this.

DeepakNewton,

Are you MuraliArun or related to MuraliArun ?

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources