Convert Date in SELECT (remove a Time)

I have the following SELECT statement on a ASP.NET page. The problem is
that the field CreatedOn contains a value like 12/9/2004 HH:MM:SS AM but all
I care about is the actual date. Is there somekind of SQL Convert function
where I can just read the date itself? "Today()" doesn't return actual time
and I don't want it. If I do that, the SELECT will never find records. I
simply want to match dates. I tried CDate(CreatedOn) but that of course
didn't work.
TIA!
SELECT [ID] FROM PurchaseOrders WHERE Status = 'I' AND POTypeID = 1 AND
CreatedOn = Today()There's a section about this in http://www.karaszi.com/SQLServer/info_datetime.asp. Note that it is
impossible to return date only as datetime datatype. Datetime always include date and time. You can
return a string, or sometimes set the time to 00:00:00.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:ul4F%239f3EHA.208@.TK2MSFTNGP12.phx.gbl...
> I have the following SELECT statement on a ASP.NET page. The problem is
> that the field CreatedOn contains a value like 12/9/2004 HH:MM:SS AM but all
> I care about is the actual date. Is there somekind of SQL Convert function
> where I can just read the date itself? "Today()" doesn't return actual time
> and I don't want it. If I do that, the SELECT will never find records. I
> simply want to match dates. I tried CDate(CreatedOn) but that of course
> didn't work.
> TIA!
> SELECT [ID] FROM PurchaseOrders WHERE Status = 'I' AND POTypeID = 1 AND
> CreatedOn = Today()
>
>|||See my answer in another thread:
Stored procedures and date comparison
--
http://www.aspfaq.com/
(Reverse address to reply.)
"D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:ul4F#9f3EHA.208@.TK2MSFTNGP12.phx.gbl...
> I have the following SELECT statement on a ASP.NET page. The problem is
> that the field CreatedOn contains a value like 12/9/2004 HH:MM:SS AM but
all
> I care about is the actual date. Is there somekind of SQL Convert
function
> where I can just read the date itself? "Today()" doesn't return actual
time
> and I don't want it. If I do that, the SELECT will never find records. I
> simply want to match dates. I tried CDate(CreatedOn) but that of course
> didn't work.
> TIA!
> SELECT [ID] FROM PurchaseOrders WHERE Status = 'I' AND POTypeID = 1 AND
> CreatedOn = Today()
>
>|||One way is to use DatePart function
SELECT [ID] FROM PurchaseOrders WHERE Status = 'I' AND POTypeID = 1 AND
DatePart(yyyy,CreatedOn) = DatePart(yyyy,GetDate()) and
DatePart(mm,CreatedOn) = DatePart(mm,GetDate()) and
DatePart(dd,CreatedOn) = DatePart(dd,GetDate())
"D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:ul4F%239f3EHA.208@.TK2MSFTNGP12.phx.gbl...
>I have the following SELECT statement on a ASP.NET page. The problem is
> that the field CreatedOn contains a value like 12/9/2004 HH:MM:SS AM but
> all
> I care about is the actual date. Is there somekind of SQL Convert
> function
> where I can just read the date itself? "Today()" doesn't return actual
> time
> and I don't want it. If I do that, the SELECT will never find records. I
> simply want to match dates. I tried CDate(CreatedOn) but that of course
> didn't work.
> TIA!
> SELECT [ID] FROM PurchaseOrders WHERE Status = 'I' AND POTypeID = 1 AND
> CreatedOn = Today()
>
>|||you can use convert as follows
convert(varchar(12),CreatedOn)=convert(varchar(12),getdate())
athi.
"D. Shane Fowlkes" wrote:
> I have the following SELECT statement on a ASP.NET page. The problem is
> that the field CreatedOn contains a value like 12/9/2004 HH:MM:SS AM but all
> I care about is the actual date. Is there somekind of SQL Convert function
> where I can just read the date itself? "Today()" doesn't return actual time
> and I don't want it. If I do that, the SELECT will never find records. I
> simply want to match dates. I tried CDate(CreatedOn) but that of course
> didn't work.
> TIA!
> SELECT [ID] FROM PurchaseOrders WHERE Status = 'I' AND POTypeID = 1 AND
> CreatedOn = Today()
>
>
>