Date/Time Question

Hello,
I do alot of searching by date alone and i currently have a column that
will be converted to a date/time field. Is there any advantage to separatin
g
the date and time into 2 columns or would this be a waste of resources?
Thanks for the input.
benForgot to add the following:
how would grouping by work? i would need to group by the date part not the
time part.
thanks again for any help!
"Ben" wrote:

> Hello,
> I do alot of searching by date alone and i currently have a column that
> will be converted to a date/time field. Is there any advantage to separat
ing
> the date and time into 2 columns or would this be a waste of resources?
> Thanks for the input.
> ben|||SQL Server does not currently have seperate date and time datatypes, so you
really have no choice in the matter...
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Ben" <ben_1_ AT hotmail DOT com> wrote in message
news:BA013859-A89F-4DA1-8253-129DEA5FEE16@.microsoft.com...
> Hello,
> I do alot of searching by date alone and i currently have a column that
> will be converted to a date/time field. Is there any advantage to
> separating
> the date and time into 2 columns or would this be a waste of resources?
> Thanks for the input.
> ben|||SELECT DATEADD(dd, DATEDIFF(dd, 0, YourDateColumn), 0) AS TheDate, <other
columns>
FROM YourTable
GROUP BY DATEADD(dd, DATEDIFF(dd, 0, YourDateColumn), 0) AS TheDate, <other
columns>
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Ben" <ben_1_ AT hotmail DOT com> wrote in message
news:0080BDC9-49FD-4BD3-872E-F03974B4D91C@.microsoft.com...
> Forgot to add the following:
>
> how would grouping by work? i would need to group by the date part not
> the
> time part.
> thanks again for any help!
> "Ben" wrote:
>|||> will be converted to a date/time field. Is there any advantage to
> separating
> the date and time into 2 columns
NO! SQL Server does not allow it anyway. If you place just the date into a
column, the time is midnight. If you place just a time, then the date is
either 1900-01-01 or 1899-12-31 depending on which tool you use.
(1899-12-30 is also a possibility, I believe.)
Instead, store the date and time in a single column. With an index on that
column, your best bet is a range query, even if you are only searching for
one day, for example:
SELECT columns FROM table
WHERE dt_column >= '20051109'
AND dt_column < '20051110'
Some further reading:
http://www.aspfaq.com/2206
http://www.aspfaq.com/2023
http://www.karaszi.com/SQLServer/info_datetime.asp|||Thank you for your replies. Seems a little complex the query but if there i
s
not other method i have no choice.
Thank you again.
"Adam Machanic" wrote:

> SQL Server does not currently have seperate date and time datatypes, so yo
u
> really have no choice in the matter...
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Ben" <ben_1_ AT hotmail DOT com> wrote in message
> news:BA013859-A89F-4DA1-8253-129DEA5FEE16@.microsoft.com...
>
>|||You can use the convert() function to perform comparisons on only the date
portion of a datetime or smalldatetime column:
select convert(char(10),getdate(),112)
20051121
"Ben" <ben_1_ AT hotmail DOT com> wrote in message
news:0080BDC9-49FD-4BD3-872E-F03974B4D91C@.microsoft.com...
> Forgot to add the following:
>
> how would grouping by work? i would need to group by the date part not
> the
> time part.
> thanks again for any help!
> "Ben" wrote:
>|||Thanks for the answer. That is what i was thinking i would have to do.
Another question: How do i group on just the date? do i use what was
mentioned by the other poster? I guess i would have to.
thanks again.
"Aaron Bertrand [SQL Server MVP]" wrote:

> NO! SQL Server does not allow it anyway. If you place just the date into
a
> column, the time is midnight. If you place just a time, then the date is
> either 1900-01-01 or 1899-12-31 depending on which tool you use.
> (1899-12-30 is also a possibility, I believe.)
> Instead, store the date and time in a single column. With an index on tha
t
> column, your best bet is a range query, even if you are only searching for
> one day, for example:
> SELECT columns FROM table
> WHERE dt_column >= '20051109'
> AND dt_column < '20051110'
> Some further reading:
> http://www.aspfaq.com/2206
> http://www.aspfaq.com/2023
> http://www.karaszi.com/SQLServer/info_datetime.asp
>
>