SQL Server Performance

Date errors

Discussion in 'General Developer Questions' started by bomaye, Sep 2, 2003.

  1. bomaye New Member

    hello.

    i've a form where you introduce a date
    <form ...
    <input name=fecha type=text ...
    with format dd/mm/yyyy

    when you use this in php

    $fecha=....

    $result=mssql_query("INSERT INTO TELEFONO fecha VALUES ($fecha)");

    when you introduce a date in the form you recieve an error like

    Warning: mssql_query(): message: Error converting data type varchar to numeric. (severity 16) in c:acopiarmeltemi elefonosalta2.php on line 42

    why i can resolve it?

    thanks.
  2. satya Moderator

    Error is self-explanatory.
    Check datatype for fecha and enclose within quotes if its varchar.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  3. bomaye New Member

    ok, i introduce into quotes, but give me a new error

    Warning: mssql_query(): message: Cannot insert the value NULL into column 'fecha_inicio', table 'porfinya.dbo.telefono'; column does not allow nulls. INSERT fails. (severity 16) in c:acopiarmeltemi elefonosalta2.php on line 44

    where i can find and change the date format into mssql.

    Thanks.
  4. bomaye New Member

    if i put a date in the form it gives me and error.

    but if i don´t introduce a date, the mssql introduce a date like
    01/06/1900

    Why??

    Thaks.[V]
  5. bambola New Member

    Try to use date format yyyy-mm-dd. It is an absolute format and should not give you errors. Wrap the date in quotes as a string. If you pass a number, it will be case to datetime and the date will be the number of pays past 1900-01-01.

    declare @d datetime
    select @d = 5
    select @d

    Bambola.
  6. bomaye New Member

    Thanks for all.


    But it should be possible to change the format?

    thanks again.

  7. Argyle New Member

    When inserting and altering dates it's recommended to use the format mentioned above. If you want to present the date in a different format in your GUI (like a web page) then use a FormatDateTime function or similar for the specific programming language you are using. I would assume PHP has one built-in or you should be able to find a free one on some PHP script site.

    /Argyle
  8. bomaye New Member

    hello,

    when i print the date that is show like that

    jun 25 2003 11:23AM

    I want to work with the timestamp unix of this date, but with seconds too.

    How i obtain a date like

    jun 25 2003 18:23:25

    if somebody know how do that, please help me.

    I use that to obtain de timestamp unix.


    $month=substr($linea3[6],0,4);
    $day=substr($linea3[6],4,2);
    $year=substr($linea3[6],6,5);
    if ($day<10) {
    $hour=substr($linea3[6],11,2);
    $min=substr($linea3[6],14,2);
    }
    else {
    $hora=substr($linea3[6],12,2);
    $min=substr($linea3[6],15,2);
    }

    $date=mktime($hora,$min,0,$mes,$dia,$anio);


    That's work very good, but i need seconds.
    thanks.
  9. Twan New Member

    the convert function has many date/time formats. See BOL for details. The one closest to what you need here seems to be 113

    which gives 08 Sep 2003 12:54:13:107

    Cheers
    Twan
  10. bomaye New Member

    thaks, but how i see BOL 113.

  11. bambola New Member

    Go to CONVERT and you will find it there. In your case like Twan said you need to use 113.
    select convert(varchar(20), getdate(), 113)

    Bambola.
  12. bomaye New Member

    where i use it??

    i have query



    $date=mssql_query("select * from Dates")
    $linea3=mssql_fetch_row($date);

    and then i use the code before.

    where i convert the date, in the select or in an other var?

    thanks
  13. Twan New Member

    it would be in the select

    select convert( varchar(20), <column name>, 113 ) from Dates

    where <column name> should be replaced with the name of the column

    NOTE if you want the other columns to also be returned then you'd need to list them (which is a best practice anyway, especially as you seem to be referring to the date column as position 6 in the code later on...?

    Cheers
    Twan

Share This Page