Update in database

This could be an easy one

I have a table with 3 fields as follows:

1) Userid - int (This is the key identity field)
2) I94Date - varchar
3) I94DateD - smalldatetime

I SEE TWO OPTIONS FOR WHAT I NEED: OPTION # 1

I need to update the I94Date for all the table, so that for record # 1 the
I94DateD is copied to field I94Date, the trick here is to instead of copying
the field as it is: 2003-08-02 00:00:00 it needs to be converted to:
08/02/2004

then go to the next record and do the same for all the table.

OR : OPTION # 2

second option is to re-format all values in the I94Date column, which are
displayed as: Aug 2 2003 12:00AM
and re-format the values to 08/02/2004

Whichever is easier. Help is greately appreciated.

A

"Aleks" <axiaxi2003 (AT) hotmail (DOT) com> wrote

I have a table with 3 fields as follows:

What kind of database is it? Below

1) Userid - int (This is the key identity field)
2) I94Date - varchar
3) I94DateD - smalldatetime

I SEE TWO OPTIONS FOR WHAT I NEED: OPTION # 1

I need to update the I94Date for all the table, so that for record #
1 the
I94DateD is copied to field I94Date, the trick here is to instead of
copying
the field as it is: 2003-08-02 00:00:00 it needs to be converted
to:
08/02/2004

then go to the next record and do the same for all the table.

UPDATE mytable SET I94Date=DATE_FORMAT(I94DateD,'*m/*d/*Y')

OR : OPTION # 2

second option is to re-format all values in the I94Date column,
which are
displayed as: Aug 2 2003 12:00AM
and re-format the values to 08/02/2004

They are stored as datetime values. You can display them however you
want in your SELECT.

SELECT DATE_FORMAT(I94DateD,'*m/*d/*Y') AS I94DateD FROM mytable

Gary

On Fri, 6 Aug 2004 23:42:17 -0400, in macromedia.dreamweaver "Aleks"
<axiaxi2003 (AT) hotmail (DOT) com> wrote:

| This could be an easy one
|
| I have a table with 3 fields as follows:
|
| 1) Userid - int (This is the key identity field)
| 2) I94Date - varchar
| 3) I94DateD - smalldatetime
|
| I SEE TWO OPTIONS FOR WHAT I NEED: OPTION # 1
|
| I need to update the I94Date for all the table, so that for record # 1 the
| I94DateD is copied to field I94Date, the trick here is to instead of copying
| the field as it is: 2003-08-02 00:00:00 it needs to be converted to:
| 08/02/2004

update mytable set I94Date = date_format(I94DateD,'%b %e %Y /%h/%i%p')

Test this on a couple of records only to see if it is what you want by
appending

WHERE userid=1

| then go to the next record and do the same for all the table.
|
| OR : OPTION # 2
|
| second option is to re-format all values in the I94Date column, which are
| displayed as: Aug 2 2003 12:00AM
| and re-format the values to 08/02/2004
|
| Whichever is easier. Help is greately appreciated.
|
| A
|

The key issue here is that formatting the database input is irrelevant. SQL
Server stores the date as a number - the number of milliseconds since the
year dot. So, as long as you've the date in a field, you use the DW
formatting options to format the output on a webpage. Also, you wouldn't
neccessarily need to do an update. Try

select cast(I94Date as smalldatetime) as I94DateD from mytable

--
Jules
http://www.charon.co.uk/charoncart
Charon Cart 3
Shopping Cart Extension for Dreamweaver MX/MX 2004

.oO(Aleks)

I have a table with 3 fields as follows:

1) Userid - int (This is the key identity field)
2) I94Date - varchar
3) I94DateD - smalldatetime

I SEE TWO OPTIONS FOR WHAT I NEED: OPTION # 1

I need to update the I94Date for all the table, so that for record # 1 the
I94DateD is copied to field I94Date, the trick here is to instead of copying
the field as it is: 2003-08-02 00:00:00 it needs to be converted to:
08/02/2004

May I ask why? Both fields contain the same data, but in a different
format. This is in most cases a sign of bad database design, since it
causes redudancy.

It would be better to store the date only once and format it as needed
when querying the data from the db, e.g.

SELECT DATE_FORMAT(I94DateD, '%m/%d/%Y') AS I94Date, ...
FROM ...

Micha

Thx Jules,

Unfortunately this only changed it to : Aug 22 2003 12:00AM
Instead of: 08/22/2003

A


"Julian Roberts" <newsg (AT) charon (DOT) co.uk> wrote

The key issue here is that formatting the database input is irrelevant.
SQL
Server stores the date as a number - the number of milliseconds since the
year dot. So, as long as you've the date in a field, you use the DW
formatting options to format the output on a webpage. Also, you wouldn't
neccessarily need to do an update. Try

select cast(I94Date as smalldatetime) as I94DateD from mytable

--
Jules
http://www.charon.co.uk/charoncart
Charon Cart 3
Shopping Cart Extension for Dreamweaver MX/MX 2004

I get the following error : Server: Msg 195, Level 15, State 10, Line 1
'date_format' is not a recognized function name.

This is MS SQL 2000 .. just in case.

A


"Jeff North" <jnorth (AT) yourpantsbigpond (DOT) net.au> wrote

On Fri, 6 Aug 2004 23:42:17 -0400, in macromedia.dreamweaver "Aleks"
axiaxi2003 (AT) hotmail (DOT) com> wrote:

| This could be an easy one
|
| I have a table with 3 fields as follows:
|
| 1) Userid - int (This is the key identity field)
| 2) I94Date - varchar
| 3) I94DateD - smalldatetime
|
| I SEE TWO OPTIONS FOR WHAT I NEED: OPTION # 1
|
| I need to update the I94Date for all the table, so that for record # 1
the
| I94DateD is copied to field I94Date, the trick here is to instead of
copying
| the field as it is: 2003-08-02 00:00:00 it needs to be converted to:
| 08/02/2004

update mytable set I94Date = date_format(I94DateD,'%b %e %Y /%h/%i%p')

Test this on a couple of records only to see if it is what you want by
appending

WHERE userid=1

| then go to the next record and do the same for all the table.
|
| OR : OPTION # 2
|
| second option is to re-format all values in the I94Date column, which
are
| displayed as: Aug 2 2003 12:00AM
| and re-format the values to 08/02/2004
|
| Whichever is easier. Help is greately appreciated.
|
| A
|

This worked though:

UPDATE mytable set
I94Date = convert(char(10), I94DateD, 101)

A


"Aleks" <axiaxi2003 (AT) hotmail (DOT) com> wrote

I get the following error : Server: Msg 195, Level 15, State 10, Line 1
'date_format' is not a recognized function name.

This is MS SQL 2000 .. just in case.

A


"Jeff North" <jnorth (AT) yourpantsbigpond (DOT) net.au> wrote in message
news:dj39h05438pn4fie2p855n974urn2jk15o (AT) 4ax (DOT) com...
On Fri, 6 Aug 2004 23:42:17 -0400, in macromedia.dreamweaver "Aleks"
axiaxi2003 (AT) hotmail (DOT) com> wrote:

| This could be an easy one
|
| I have a table with 3 fields as follows:
|
| 1) Userid - int (This is the key identity field)
| 2) I94Date - varchar
| 3) I94DateD - smalldatetime
|
| I SEE TWO OPTIONS FOR WHAT I NEED: OPTION # 1
|
| I need to update the I94Date for all the table, so that for record #
1
the
| I94DateD is copied to field I94Date, the trick here is to instead of
copying
| the field as it is: 2003-08-02 00:00:00 it needs to be converted
to:
| 08/02/2004

update mytable set I94Date = date_format(I94DateD,'%b %e %Y /%h/%i%p')

Test this on a couple of records only to see if it is what you want by
appending

WHERE userid=1

| then go to the next record and do the same for all the table.
|
| OR : OPTION # 2
|
| second option is to re-format all values in the I94Date column, which
are
| displayed as: Aug 2 2003 12:00AM
| and re-format the values to 08/02/2004
|
| Whichever is easier. Help is greately appreciated.
|
| A
|

On Sat, 7 Aug 2004 20:35:52 -0400, in macromedia.dreamweaver "Aleks"
<axiaxi2003 (AT) hotmail (DOT) com> wrote:

| I get the following error : Server: Msg 195, Level 15, State 10, Line 1
| 'date_format' is not a recognized function name.
|
| This is MS SQL 2000 .. just in case.
|
| A

The date_format function is a mySQL function. You'll need to find the
equivalent MS SQL function.

| "Jeff North" <jnorth (AT) yourpantsbigpond (DOT) net.au> wrote in message
| news:dj39h05438pn4fie2p855n974urn2jk15o (AT) 4ax (DOT) com...
| > On Fri, 6 Aug 2004 23:42:17 -0400, in macromedia.dreamweaver "Aleks"
| > <axiaxi2003 (AT) hotmail (DOT) com> wrote:
|
| > >| This could be an easy one
| > >|
| > >| I have a table with 3 fields as follows:
| > >|
| > >| 1) Userid - int (This is the key identity field)
| > >| 2) I94Date - varchar
| > >| 3) I94DateD - smalldatetime
| > >|
| > >| I SEE TWO OPTIONS FOR WHAT I NEED: OPTION # 1
| > >|
| > >| I need to update the I94Date for all the table, so that for record # 1
| the
| > >| I94DateD is copied to field I94Date, the trick here is to instead of
| copying
| > >| the field as it is: 2003-08-02 00:00:00 it needs to be converted to:
| > >| 08/02/2004
|
| > update mytable set I94Date = date_format(I94DateD,'%b %e %Y /%h/%i%p')
|
| > Test this on a couple of records only to see if it is what you want by
| > appending
|
| > WHERE userid=1
|
| > >| then go to the next record and do the same for all the table.
| > >|
| > >| OR : OPTION # 2
| > >|
| > >| second option is to re-format all values in the I94Date column, which
| are
| > >| displayed as: Aug 2 2003 12:00AM
| > >| and re-format the values to 08/02/2004
| > >|
| > >| Whichever is easier. Help is greately appreciated.
| > >|
| > >| A
| > >|
|
|