Dreamweaver - Update in database

Author

Update in database

Aleks

2004-08-07, 4:14 am

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

Gary White

2004-08-07, 7:14 am

"Aleks" <axiaxi2003@hotmail.com> wrote in message
news:cf1j2p$3lg$1@forums.macromedia.com...
>
> 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

Jeff North

2004-08-07, 7:14 am

Julian Roberts

2004-08-07, 7:14 am

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

Michael Fesser

2004-08-07, 7:15 pm

.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

Aleks

2004-08-07, 11:14 pm

Thx Jules,

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

A

"Julian Roberts" <newsg@charon.co.uk> wrote in message
news:cf25o5$ia0$1@forums.macromedia.com...
> 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
>
>

Aleks

2004-08-07, 11:14 pm

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@yourpantsbigpond.net.au> wrote in message
news:dj39h05438pn4fie2p855n974urn2jk15o@4ax.com...
> On Fri, 6 Aug 2004 23:42:17 -0400, in macromedia.dreamweaver "Aleks"
> <axiaxi2003@hotmail.com> wrote:
>
the[color=darkred]
copying[color=darkred]
>
> 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
>
are[color=darkred]
>

Aleks

2004-08-07, 11:14 pm

This worked though:

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

A

"Aleks" <axiaxi2003@hotmail.com> wrote in message
news:cf3sh9$so3$1@forums.macromedia.com...
> 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@yourpantsbigpond.net.au> wrote in message
> news:dj39h05438pn4fie2p855n974urn2jk15o@4ax.com...
1[color=darkred]
> the
> copying
to:[color=darkred]
> are
>
>

Jeff North

2004-08-08, 4:14 am

On Sat, 7 Aug 2004 20:35:52 -0400, in macromedia.dreamweaver "Aleks"
<axiaxi2003@hotmail.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@yourpantsbigpond.net.au> wrote in message
>| news:dj39h05438pn4fie2p855n974urn2jk15o@4ax.com...
>| > On Fri, 6 Aug 2004 23:42:17 -0400, in macromedia.dreamweaver "Aleks"
>| > <axiaxi2003@hotmail.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
>| > >|
>| >
>|