Professional Web Applications Themes

Converting integers to datetime - MySQL

I have the following fields in a database: year integer(4) not null default '0' month integer(2) not null default '0' day integer(2) not null default '0' hour integer(2) not null default '0' minute integer(2) not null default '0' and just added (using alter table) the following blank field: date datetime not null default '0' I want to use (year, month, day, hour, minute) to fill in the datetime field using the MySQL monitor. How can this be done? Thanks! Pete...

  1. #1

    Default Converting integers to datetime

    I have the following fields in a database:

    year integer(4) not null default '0'
    month integer(2) not null default '0'
    day integer(2) not null default '0'
    hour integer(2) not null default '0'
    minute integer(2) not null default '0'

    and just added (using alter table) the following blank field:

    date datetime not null default '0'

    I want to use (year, month, day, hour, minute) to fill in the datetime field
    using the MySQL monitor. How can this be done?

    Thanks!
    Pete
    Peter Guest

  2. #2

    Default Re: Converting integers to datetime

    Peter wrote:
    > I have the following fields in a database:
    >
    > year integer(4) not null default '0'
    > month integer(2) not null default '0'
    > day integer(2) not null default '0'
    > hour integer(2) not null default '0'
    > minute integer(2) not null default '0'
    >
    > and just added (using alter table) the following blank field:
    >
    > date datetime not null default '0'
    >
    > I want to use (year, month, day, hour, minute) to fill in the datetime field
    > using the MySQL monitor. How can this be done?
    >
    > Thanks!
    > Pete
    update tablename
    set `date` = concat(
    lpad( `year`, 4,'0' ), '-',
    lpad( `month`, 2,'0' ), '-',
    lpad( `day`, 2,'0' ), ' ',
    lpad( `hour`, 2,'0' ), ':',
    lpad( `minute`, 2,'0' ), ':00'
    );

    Side note: Don't call your field 'date'. It's a reserved word.
    Even though MySQL is forgiving, you can face subtle errors if you
    are not extra careful.

    ciao
    gmax

    --
    _ _ _ _
    (_|| | |(_|><
    _|
    [url]http://gmax.oltrelinux.com[/url]
    Giuseppe Maxia Guest

  3. #3

    Default Re: Converting integers to datetime

    Giuseppe Maxia <gmax__cpan_._org> wrote:
    > Peter wrote:
    >> I have the following fields in a database:
    >>
    >> year integer(4) not null default '0'
    >> month integer(2) not null default '0'
    >> day integer(2) not null default '0'
    >> hour integer(2) not null default '0'
    >> minute integer(2) not null default '0'
    >>
    >> and just added (using alter table) the following blank field:
    >>
    >> date datetime not null default '0'
    >>
    >> I want to use (year, month, day, hour, minute) to fill in the datetime field
    >> using the MySQL monitor. How can this be done?
    >>
    >> Thanks!
    >> Pete
    >
    > update tablename
    > set `date` = concat(
    > lpad( `year`, 4,'0' ), '-',
    > lpad( `month`, 2,'0' ), '-',
    > lpad( `day`, 2,'0' ), ' ',
    > lpad( `hour`, 2,'0' ), ':',
    > lpad( `minute`, 2,'0' ), ':00'
    > );
    >
    > Side note: Don't call your field 'date'. It's a reserved word.
    > Even though MySQL is forgiving, you can face subtle errors if you
    > are not extra careful.
    >
    > ciao
    > gmax
    Hmmm. Never heard of lpad. I'll go look that up now.

    Point taken with the field name. You're absolutely right; I'll change it.

    A big thanks for the reply! :)

    Pete
    Peter Jay Salzman Guest

  4. #4

    Default Re: Converting integers to datetime

    Peter schrieb:
    > I have the following fields in a database:
    >
    > year integer(4) not null default '0'
    > month integer(2) not null default '0'
    > day integer(2) not null default '0'
    > hour integer(2) not null default '0'
    > minute integer(2) not null default '0'
    >
    > and just added (using alter table) the following blank field:
    >
    > date datetime not null default '0'
    >
    > I want to use (year, month, day, hour, minute) to fill in the datetime field
    > using the MySQL monitor. How can this be done?
    >
    By using the appropriate UPDATE statement, I'd guess. Like so (untested)

    UPDATE table
    SET date <----------- bad, bad choice for a name.
    = concat(year,'-',month,'-',day,' ',hour,':',second)

    You might want to read the doentation concerning the use of
    reserved names, the date-time and string functions, and the format for
    date/time literals. The doentation is available at dev.mysql.com/doc
    Christian Kirsch Guest

Similar Threads

  1. Arithmetic overflow error converting expression to datatype datetime
    By dgiet in forum Coldfusion Database Access
    Replies: 2
    Last Post: January 8th, 10:24 PM
  2. Display integers in CFChart
    By Waimea Wilbur in forum Coldfusion - Advanced Techniques
    Replies: 0
    Last Post: June 28th, 09:14 PM
  3. Which module formats runs of integers a la 1, 4-7, 8, 10?
    By Ron Savage in forum PERL Modules
    Replies: 6
    Last Post: April 10th, 12:40 AM
  4. voidP() and variable = VOID with integers with a value of 0
    By Scott Southworth in forum Macromedia Director Lingo
    Replies: 0
    Last Post: August 8th, 04:58 PM
  5. getting decimal places when dividing integers
    By in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 3rd, 05:08 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139