How to remove time from date?

Hello,
I have a table (T1) that has a field that holds a date with a time. In
another table (T2) I have records related to T1 on an ID field (one T1 to
many T2). T2 has a date field also, but this field does not have time (ie
the time part is midnight). I want to join the two tables on the ID and
Date but the time portions of the dates are not equal so they do not match.
SELECT T1.ID, T2.Date, T2.Value
FROM T1
JOIN T2
ON T1.ID = T2.ID AND T1.Date = T2.Date
Does anyone know how I can set the time portion of T1.Date to midnight in
the query (not in the table) so the join will work.
I've tried CONVERT(DATETIME, T1.Date, 106) but it keeps the time part. I
want the time part dropped or midnight.
Thanks
EdmundHi,
Try this...
SELECT T1.ID, T2.Date, T2.Value
FROM T1
JOIN T2
ON T1.ID = T2.ID AND Convert(varchar(13), T1.Date, 112) = Convert
(Varchar(13), T2.Date, 112)
HTH
Barry|||I don't know why I did not think of this. It was staring right at me.
Thanks Barry. I've gone for CONVERT(CHAR(10),T1.Date, 120)) as it will
implicitly convert to DATETIME with date as midnight.
Edmund.
"Barry" <barry.oconnor@.manx.net> wrote in message
news:1149517628.941905.154220@.h76g2000cwa.googlegroups.com...
> Hi,
> Try this...
> SELECT T1.ID, T2.Date, T2.Value
> FROM T1
> JOIN T2
> ON T1.ID = T2.ID AND Convert(varchar(13), T1.Date, 112) = Convert
> (Varchar(13), T2.Date, 112)
>
> HTH
> Barry
>|||try this:
CONVERT(VARCHAR(10), T1.date, 126)
Barry wrote:
> Hi,
> Try this...
> SELECT T1.ID, T2.Date, T2.Value
> FROM T1
> JOIN T2
> ON T1.ID = T2.ID AND Convert(varchar(13), T1.Date, 112) = Convert
> (Varchar(13), T2.Date, 112)
>
> HTH
> Barry|||Edmund,
You might want to chk this article :: http://www.aspfaq.com/show.asp?id=2460
Extract from that article:
--
For example, to get today's date in YYYYMMDD format, you currently need to
call the following:
SELECT CONVERT(CHAR(8), GETDATE(), 112)
What does the 112 mean? Nothing. It's just an arbitrary number representing
this specific format.
Best Regards
Vadivel
http://vadivel.blogspot.com
"Edmund" wrote:

> Hello,
> I have a table (T1) that has a field that holds a date with a time. In
> another table (T2) I have records related to T1 on an ID field (one T1 to
> many T2). T2 has a date field also, but this field does not have time (ie
> the time part is midnight). I want to join the two tables on the ID and
> Date but the time portions of the dates are not equal so they do not match
.
> SELECT T1.ID, T2.Date, T2.Value
> FROM T1
> JOIN T2
> ON T1.ID = T2.ID AND T1.Date = T2.Date
> Does anyone know how I can set the time portion of T1.Date to midnight in
> the query (not in the table) so the join will work.
> I've tried CONVERT(DATETIME, T1.Date, 106) but it keeps the time part. I
> want the time part dropped or midnight.
> Thanks
> Edmund
>
>|||Edmund,
To truly remove time from datetime and not just "hide" it:
SELECT CAST(DATEDIFF(DAY,0,getDate()) AS DATETIME)
Returns:
2006-06-06 00:00:00.000
Mark|||A slightly better way to do it than using the CONVERT() function calls
would be:
SELECT T1.ID, T2.Date, T2.Value
FROM T1
INNER JOIN T2
ON T1.ID = T2.ID
AND DATEADD(d, DATEDIFF(d, 0, T1.Date), 0) = DATEADD(d,
DATEDIFF(d, 0, T2.Date), 0)
If you want more info on it, Tibor Karaszi wrote a pretty good web
article about datetime data including a short section on stripping time
info:
http://www.karaszi.com/SQLServer/in...idOfTimePortion
*mike hodgson*
http://sqlnerd.blogspot.com
Barry wrote:

>Hi,
>Try this...
> SELECT T1.ID, T2.Date, T2.Value
> FROM T1
> JOIN T2
> ON T1.ID = T2.ID AND Convert(varchar(13), T1.Date, 112) = Convert
>(Varchar(13), T2.Date, 112)
>
>HTH
>Barry
>
>|||Mike, this is very good.
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message news:u4N4C8PiGHA.1612@.
TK2MSFTNGP04.phx.gbl...
A slightly better way to do it than using the CONVERT() function calls would
be:
SELECT T1.ID, T2.Date, T2.Value
FROM T1
INNER JOIN T2
ON T1.ID = T2.ID
AND DATEADD(d, DATEDIFF(d, 0, T1.Date), 0) = DATEADD(d, DATEDIFF(d, 0, T2.Da
te), 0)
If you want more info on it, Tibor Karaszi wrote a pretty good web article a
bout datetime data including a short section on stripping time info:
http://www.karaszi.com/SQLServer/in...idOfTimePortion
mike hodgson
http://sqlnerd.blogspot.com
Barry wrote:
Hi,
Try this...
SELECT T1.ID, T2.Date, T2.Value
FROM T1
JOIN T2
ON T1.ID = T2.ID AND Convert(varchar(13), T1.Date, 112) = Convert
(Varchar(13), T2.Date, 112)
HTH
Barry