DateTime Value Problem

[xterra]

Hi,

I am trying to retrieve a value from a datetime field. When I stored the value using visual basic it was just a blank datetime value which was stored as 12:00:00 AM . Now when I am trying to select records based on the same value it doesn't retrieve it.

ie: SELECT * FROM table WHERE datetimefield = '12:00:00 AM'

I know that is the value it has because when i use the enterprise manager to look at the data it has that stored in there. Any ideas on how I can build a select statement based on records with the 12:00:00 AM value?

Thanks,

Adam

 

[tlbroadbent]

Here is one solution.

Use convert to convert the column to character style 108 (hh:mm:ss) and set the criteria to '00:00:00' based on a 24 hour clock.

SELECT * FROM table
WHERE convert(char(10),datetimefield,108) = '00:00:00' Terry Broadbent

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[nlc]

In SQL 2000, default dates (those inserted with a '' as opposed to those explicitly set to NULL) are saved as

1900-01-01 00:00:00.000

If I use the following query, I can extract those dates:

select * from tmp where
convert(char(8), testDate, 108) = '00:00:00'

The format mask 108 is used to get just the time.

 

[xterra]

Well I tried the code you both have provided and it returns all of the records in the table. One of the problems may be that I am not just giving it a " value I am giving it the empty value visual basic gives to date fields which is 12:00:00 AM. I would assume that SQL Server would take the value as it is and you should be able to select based on the same value but you can't. Does this make any more sense? Let me know if I can give you any further details to help.

Thanks for all your help thusfar,

Adam