Problem solve Get help with specific problems with your technologies, process and projects.

Selecting specific DATETIME values

I have a database with a date/time stamp called dtstatusdatum. I would like to extract statistics from the database...

based on this column. What should the select clause look like. I tried:

select count(*)
from mytable
where dtstatusdatum like '2002-07-29%'

but get no results to the query:

(0) rows affected.

Thanks for mentioning "(0) rows affected" which tells me you're probably working with Microsoft SQL/Server. There are several ways to approach your query, but here's a simple one:

select count(*)
  from mytable
 where convert(dtstatusdatum,char(10),120)
     = '2002-07-29'

See the Microsoft documentation for CAST and CONVERT. Style 120 is the yyyy-mm-dd hh:mm:ss format, and converting to char(10) truncates the time off the end. Thus you can do a straight equality comparison rather than using LIKE.

For More Information

This was last published in August 2002

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.