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
 select statement using datetime field

Author  Topic 

nbk166r
Starting Member

4 Posts

Posted - 2011-05-25 : 11:27:51
I want to write a select statement to search for fields including date. The date field appears as: 5/24/2011 11:00:15.137 PM

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-25 : 11:35:13
You mean you want a particular date?

where datediff(dd,datefld,'20110525') = 0
or
where datefld >= '20110525'
and datefld < '20110526'

The second option has a chance of using an index partial scan.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-25 : 11:36:23
That doesn't look like a SQL Server date, but
SELECT <stuff>
FROM yourTable
WHERE datefield = '5/24/2011 11:00:15.137 PM'

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

nbk166r
Starting Member

4 Posts

Posted - 2011-05-25 : 11:40:01
quote:
Originally posted by nbk166r

I want to write a select statement to search for fields including date. The date field appears as: 5/24/2011 11:00:15.137 PM



Essentially, I want the select statement to be based on Date ONLY. How can I choose recordsets that are only based on a specific date, like 05/24/2011.
Go to Top of Page

nbk166r
Starting Member

4 Posts

Posted - 2011-05-25 : 11:44:51
The 'asOfDate' datetime table column has the following format: 3/21/2011 12:00:00.000 AM.
So, I need to write a select statement that pulls all recordsets with date >=05/20/2011. How can I do that ?
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-25 : 11:48:46
use Nigel's

where datefld >= '20110525'

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

nbk166r
Starting Member

4 Posts

Posted - 2011-05-25 : 13:51:49
and what if I have bunch od different dates and I want to only grab the recordsets of the latest date ?
Something like:
2/18/2011 12:00:00.000 AM
2/21/2011 12:00:00.000 AM
2/22/2011 12:00:00.000 AM
2/23/2011 12:00:00.000 AM
2/24/2011 12:00:00.000 AM
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-25 : 14:03:31
That's a different problem than in your original post. If you tell us what you want we can answer it for you. So, what is it that you want?

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-25 : 14:41:27
Use row_num() to order the rows by date descending (partitioning by whatever you want) and select where the value is 1.

First of all decide the requirements - you have no hope of writing the code unless you kmow the objective.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
   

- Advertisement -