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

how to remove the sql server registry mess?

after uninstalling the sql server and related stuff, I found tons of sql
server registry entries.
How do remove them all properly? any script or tools. I tried the setup
form disk to remove but still no joyHi
You don't give the version you are trying to remove, but the following may
help http://msdn2.microsoft.com/en-us/library/aa337087.aspx may be useful if
it is SQL 2005 or http://support.microsoft.com/?id=290991 for SQL 2000.
John
"gs" wrote:

> after uninstalling the sql server and related stuff, I found tons of sql
> server registry entries.
> How do remove them all properly? any script or tools. I tried the setup
> form disk to remove but still no joy
>
>|||thank you. I need the info the 2005
what about the express version also
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3AF5AC75-F591-44D5-B2FA-EC696F21B2C7@.microsoft.com...[vbcol=seagreen]
> Hi
> You don't give the version you are trying to remove, but the following may
> help http://msdn2.microsoft.com/en-us/library/aa337087.aspx may be useful
> if
> it is SQL 2005 or http://support.microsoft.com/?id=290991 for SQL 2000.
> John
> "gs" wrote:
>|||Hi
I would expect SQLExpress to be the same or very similar.
John
"gs" wrote:

> thank you. I need the info the 2005
>
> what about the express version also
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:3AF5AC75-F591-44D5-B2FA-EC696F21B2C7@.microsoft.com...
>
>sql

how to remove the sql server registry mess?

after uninstalling the sql server and related stuff, I found tons of sql
server registry entries.
How do remove them all properly? any script or tools. I tried the setup
form disk to remove but still no joy
Hi
You don't give the version you are trying to remove, but the following may
help http://msdn2.microsoft.com/en-us/library/aa337087.aspx may be useful if
it is SQL 2005 or http://support.microsoft.com/?id=290991 for SQL 2000.
John
"gs" wrote:

> after uninstalling the sql server and related stuff, I found tons of sql
> server registry entries.
> How do remove them all properly? any script or tools. I tried the setup
> form disk to remove but still no joy
>
>
|||thank you. I need the info the 2005
what about the express version also
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3AF5AC75-F591-44D5-B2FA-EC696F21B2C7@.microsoft.com...[vbcol=seagreen]
> Hi
> You don't give the version you are trying to remove, but the following may
> help http://msdn2.microsoft.com/en-us/library/aa337087.aspx may be useful
> if
> it is SQL 2005 or http://support.microsoft.com/?id=290991 for SQL 2000.
> John
> "gs" wrote:
|||Hi
I would expect SQLExpress to be the same or very similar.
John
"gs" wrote:

> thank you. I need the info the 2005
>
> what about the express version also
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:3AF5AC75-F591-44D5-B2FA-EC696F21B2C7@.microsoft.com...
>
>

how to remove the sql server registry mess?

after uninstalling the sql server and related stuff, I found tons of sql
server registry entries.
How do remove them all properly? any script or tools. I tried the setup
form disk to remove but still no joyHi
You don't give the version you are trying to remove, but the following may
help http://msdn2.microsoft.com/en-us/library/aa337087.aspx may be useful if
it is SQL 2005 or http://support.microsoft.com/?id=290991 for SQL 2000.
John
"gs" wrote:
> after uninstalling the sql server and related stuff, I found tons of sql
> server registry entries.
> How do remove them all properly? any script or tools. I tried the setup
> form disk to remove but still no joy
>
>|||thank you. I need the info the 2005
what about the express version also
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3AF5AC75-F591-44D5-B2FA-EC696F21B2C7@.microsoft.com...
> Hi
> You don't give the version you are trying to remove, but the following may
> help http://msdn2.microsoft.com/en-us/library/aa337087.aspx may be useful
> if
> it is SQL 2005 or http://support.microsoft.com/?id=290991 for SQL 2000.
> John
> "gs" wrote:
>> after uninstalling the sql server and related stuff, I found tons of sql
>> server registry entries.
>> How do remove them all properly? any script or tools. I tried the setup
>> form disk to remove but still no joy
>>|||Hi
I would expect SQLExpress to be the same or very similar.
John
"gs" wrote:
> thank you. I need the info the 2005
>
> what about the express version also
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:3AF5AC75-F591-44D5-B2FA-EC696F21B2C7@.microsoft.com...
> > Hi
> >
> > You don't give the version you are trying to remove, but the following may
> > help http://msdn2.microsoft.com/en-us/library/aa337087.aspx may be useful
> > if
> > it is SQL 2005 or http://support.microsoft.com/?id=290991 for SQL 2000.
> >
> > John
> >
> > "gs" wrote:
> >
> >> after uninstalling the sql server and related stuff, I found tons of sql
> >> server registry entries.
> >>
> >> How do remove them all properly? any script or tools. I tried the setup
> >> form disk to remove but still no joy
> >>
> >>
> >>
>
>