Datetime Column Problem

Hi All,
Here is my situation:
I have a table which has a column of type datetime and it carries data
with timestamp in it. For example: 2004-08-16 16:09:56.120
I have another column which is also of type datetime but contains data
with no time values (because someone didn't pay much attention). For
example: 2004-08-16 00:00:00.000
Here is my problem:
I have about 50 stored procs where these columns are compared for
example: subj_svd_visit_date < subj_budget_start_date etc.
What is the best way to approach this problem so that I don't have to
make change in the 50 procs.
Just to note that in some places people are using GetDate() when they
are inserting values into these columns.
Thanks very much for your input.
*** Sent via Developersdex http://www.examnotes.net ***You can start here:
http://www.karaszi.com/sqlserver/info_datetime.asp
-oj
"Vik Mohindra" <vikmohindra@.hotmail.com> wrote in message
news:eHG$nxVQFHA.3544@.TK2MSFTNGP12.phx.gbl...
> Hi All,
> Here is my situation:
> I have a table which has a column of type datetime and it carries data
> with timestamp in it. For example: 2004-08-16 16:09:56.120
> I have another column which is also of type datetime but contains data
> with no time values (because someone didn't pay much attention). For
> example: 2004-08-16 00:00:00.000
> Here is my problem:
> I have about 50 stored procs where these columns are compared for
> example: subj_svd_visit_date < subj_budget_start_date etc.
> What is the best way to approach this problem so that I don't have to
> make change in the 50 procs.
> Just to note that in some places people are using GetDate() when they
> are inserting values into these columns.
> Thanks very much for your input.
> *** Sent via Developersdex http://www.examnotes.net ***|||I don't see the problem. This is still a valid datetime value: 2004-08-16
00:00:00.000. It simply has a time of midnight. All comparisons and such
are still very much valid against that value.
Andrew J. Kelly SQL MVP
"Vik Mohindra" <vikmohindra@.hotmail.com> wrote in message
news:eHG$nxVQFHA.3544@.TK2MSFTNGP12.phx.gbl...
> Hi All,
> Here is my situation:
> I have a table which has a column of type datetime and it carries data
> with timestamp in it. For example: 2004-08-16 16:09:56.120
> I have another column which is also of type datetime but contains data
> with no time values (because someone didn't pay much attention). For
> example: 2004-08-16 00:00:00.000
> Here is my problem:
> I have about 50 stored procs where these columns are compared for
> example: subj_svd_visit_date < subj_budget_start_date etc.
> What is the best way to approach this problem so that I don't have to
> make change in the 50 procs.
> Just to note that in some places people are using GetDate() when they
> are inserting values into these columns.
> Thanks very much for your input.
> *** Sent via Developersdex http://www.examnotes.net ***|||Thanks oj for the link, it is very helpful.
Thanks Kelly for looking into the problem. You are right that there is
no problem on the surface but the time that one of the field is storing
is not needed. That is what my question was. Given that now one of the
field stores time value that is not needed, what do I do to get rid off
it and what do I do to the code that compares it.
*** Sent via Developersdex http://www.examnotes.net ***|||Not sure I understand what you are asking. The Link OJ posted should answer
most questions about using datetime. If you are asking how to make all
datetime values store midnight and retain the date portion you can do
something like this:
SELECT CONVERT(DATETIME,CONVERT(VARCHAR(8),Your
DateTimeCol,112))
Andrew J. Kelly SQL MVP
"Vik Mohindra" <vikmohindra@.hotmail.com> wrote in message
news:ezgs55ZQFHA.244@.TK2MSFTNGP12.phx.gbl...
> Thanks oj for the link, it is very helpful.
> Thanks Kelly for looking into the problem. You are right that there is
> no problem on the surface but the time that one of the field is storing
> is not needed. That is what my question was. Given that now one of the
> field stores time value that is not needed, what do I do to get rid off
> it and what do I do to the code that compares it.
>
> *** Sent via Developersdex http://www.examnotes.net ***