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]

Re: HowTo Update Date Field

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]

Re: HowTo Update Date Field

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" <REMOVE_BEFORE_ REPLYING_dporta s@acm.org> wrote in message
news:JvmdnbwuWp HPwz_cRVn-iw@giganews.com ...[color=blue]
> 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
> --
>
>[/color]

[Erland Sommarskog]

Re: HowTo Update Date Field

fwiest (fred@fredwiest .com) writes:[color=blue]
> 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.[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[fwiest]

Re: HowTo Update Date Field

Thanks exactly what I was looking for. Thank you very much.


"Erland Sommarskog" <esquel@sommars kog.se> wrote in message
news:Xns95A9F2E 3F896DYazorman@ 127.0.0.1...[color=blue]
> fwiest (fred@fredwiest .com) writes:[color=green]
>> 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.[/color]
>
> 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, esquel@sommarsk og.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp[/color]