query datetime

[vbbit]

I have a register date column and property is set to datetime.

I want to run a query on users that registered today, but don't know how to. The date time shows, for example 01/30/2010 10:21:56.000 PM

I don't know how to query that out? I am not interested in time.

[jmcilhinney]

What database?

[vbbit]

sql server, sorry for late response.

[jmcilhinney]

If you want to get records where a field containing times falls on a specific date then you need to use a range. You need to specify a date with the time zeroed and then use indefinite comparisons, e.g. <, etc. The fact that you are looking for records from today makes it easier because you don't need to specify an upper limit, i.e. there can't be any records from later than today. So, your SQL will look something like this:

SELECT *
FROM MyTable
WHERE MyDate >= @MyDate

In your VB code, you'll set that parameter value using Date.Today, which is today's date with the time zeroed.

In a more complex case, where you wanted to be able to specify any date, your SQL would look like this:

SELECT *
FROM MyTable
WHERE MyDate >= @StartDate
AND MyDate < @EndDate

In your VB code you'd get a Date value and use myDate.Date and myDate.Date.AddDays(1) to set the parameter values.

Note that that means that your SQL can handle any date range and its up to your VB code to process the dates correctly. You could also write the SQL code such that it took any single date and processed it internally, zeroing the time and adding a day for the upper limit. That way you ensure that the SQL will only ever give you a single day's range. That's not hard but it is a bit more complex, so it's up to you where you think it's most appropriate to do the processing. If the SQL will be used in multiple places then the SQL should do it, otherwise I'd stick to the easy option and do it in the VB code.

[Kasracer]

If you're looking for a specific day and this is an ad-hoc query, you could use a CAST to date and compare that way.

SELECT *
FROM MyTable
WHERE CAST(MyDate AS DATE) = @SpecifiedDate

Another option is to use DATEPART and compare the year, month and day.

Naturally, using a range as jmcjmcjmcncmcmncmcmmj suggested would be better performance wise if this is a DATETIME field. I just wanted to offer some alternatives in-case this is a quick, ad-hoc query .