Weird datetime behaviour - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Weird datetime behaviour

Author

Topic

Gareth_Hastings
Starting Member

7 Posts

Posted - 2007-12-20 : 12:28:41

Right I'm sure this should be dead simple and I'm probably missing something but here goes.

I have a table with 2 fields. t_start and t_end, both are datetime fields. My table has 1 row.


create table [dbo].[weirdtime](
	[t_start] [datetime] not null,
	[t_end] [datetime] not null
) on [primary]

insert into weirdtime (t_start, t_end) values ('1899-12-30 00:00:00', '1899-12-30 17:15:00')

So I have a query that selects everything in this table where t_start is not equal to 1899-12-30 00:00:00 AND t_end is not equal to 1899-12-30 23:59:59. Simple eh?


select * from weirdtime where t_start <> '1899-12-30 00:00:00' and t_end <> '1899-12-30 23:59:59'

This returns 0 rows. I must be missing something simple here.

I've tried

select * from weirdtime where (t_start <> convert(datetime, '1899-12-30 00:00:00', 102)) and (t_end <> convert(datetime, '1899-12-30 23:59:59', 102))

this doesn't work.

Any ideas?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-20 : 12:36:03

Didnt understand what you are expecting here? Its returning 0 rows as expected. The first condition itself becomes false and so record wont be returned.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-20 : 13:07:46

select * from weirdtime where t_start <> '1899-12-30 00:00:00' OR t_end <> '1899-12-30 23:59:59'

E 12°55'05.25"
N 56°04'39.16"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-12-20 : 13:09:14

You can change the AND to an OR and get the row back. However, are you trying to do something specific or are you just working to understand boolean logic?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-20 : 13:20:53

select * from weirdtime where NOT(t_start = '1899-12-30 00:00:00' and t_end = '1899-12-30 23:59:59')

EDIT: Typo or copy&paste error

E 12°55'05.25"
N 56°04'39.16"

Gareth_Hastings
Starting Member

7 Posts

Posted - 2007-12-20 : 15:31:09

I was just having a stupid moment thanks for reminding me.

I swapped the and for an or

Peso: Your last query you need to swap the <> for =

Again thanks

Gareth

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources