HowTo Update Date Field

[fwiest]

Could someone help me with the most efficient way to update a date field. I
have a field with Date and Time in it but I want to update it with only the
Date and strip off the time. Any ideas for the shortest way to do this?

[David Portas]

DATETIME / SMALLDATETIME columns always contain both date and time. Use the
format 'YYYYMMDD' (no spaces) to define the date value. The time will
default to midnight. For example:

UPDATE YourTable
SET date_col = '20041122'
WHERE ...

You can format the value without the time for display and printing purposes.
Usually your application program is the best place to format the date.

--
David Portas
SQL Server MVP
--

[fwiest]

My problem is that I have the date/time already in the table and I need to
do all the formating within the SQL statement.

"David Portas" <RE****************************@acm.org> wrote in message
news:Jv********************@giganews.com...

DATETIME / SMALLDATETIME columns always contain both date and time. Use
the format 'YYYYMMDD' (no spaces) to define the date value. The time will
default to midnight. For example:

UPDATE YourTable
SET date_col = '20041122'
WHERE ...

You can format the value without the time for display and printing
purposes. Usually your application program is the best place to format the
date.

--
David Portas
SQL Server MVP
--

[Erland Sommarskog]

fwiest (fr**@fredwiest.com) writes:

My problem is that I have the date/time already in the table and I need to
do all the formating within the SQL statement.

Your question was about updating. And you cannot change the format of a
datetime column in SQL Server. A datetime value is always stored in a
binary form.

You can do some formattinig when you select the data. For instance to
get the date portion only:

SELECT convert(char(8), datetimecol, 112) FROM tbl
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[fwiest]

Thanks exactly what I was looking for. Thank you very much.
"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...

fwiest (fr**@fredwiest.com) writes:

My problem is that I have the date/time already in the table and I need
to
do all the formating within the SQL statement.

Your question was about updating. And you cannot change the format of a
datetime column in SQL Server. A datetime value is always stored in a
binary form.

You can do some formattinig when you select the data. For instance to
get the date portion only:

SELECT convert(char(8), datetimecol, 112) FROM tbl
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp