sql server programming: Datetime Column Problem -- sql server programming

You're in the

sql server programming

group:

Datetime Column Problem

Vik Mohindra
4/14/2005 6:42:37 PM

sql server programming:

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.

Re: Datetime Column Problem

oj
4/14/2005 7:20:53 PM

You can start here:
http://www.karaszi.com/sqlserver/info_datetime.asp

--
-oj

[quoted text, click to view]

Re: Datetime Column Problem

Andrew J. Kelly
4/14/2005 10:30:24 PM

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

[quoted text, click to view]

Re: Datetime Column Problem

Vik Mohindra
4/15/2005 2:35:33 AM

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.

Re: Datetime Column Problem

Andrew J. Kelly
4/15/2005 10:07:25 AM

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),YourDateTimeCol,112))

--
Andrew J. Kelly SQL MVP

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.

View Other Months
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008

home · blog · groups

Questions? Comments? Contact the dn