Results 1 to 4 of 4

Thread: Date/time problem in SQL server 7.0

  1. #1
    Geoff Taylor Guest

    Date/time problem in SQL server 7.0

    Hi folks,
    I posted the following question on comp.databases.ms-sqlserver,
    bu, to my surprise got no responses. So I wondered if you good
    people might be able to help us out...
    ==================================================
    Our application communicates with SQLServer 7 through ODBC. In
    order to write date/time fields into the database, it uses either the dt
    conversion function, or more usually the ts conversion, on a string of
    the format 'yyyy-mm-dd hh:mm:ss.xxxxxx'. With almost every every
    other database engine (I think Informix is the only exception), this is
    accepted and converted into a date format that the back end can store.

    With SQLServer 7, any inserts containing dates are dropped because the
    timestamp conversion isn't working. If I take a copy of the select statement
    into the query analyser, it reports an error unless I change the timestamp
    string format from ts 'yyyy-mm-dd hh:mm:ss.xxxxxx' to ts 'yyyy-mm-dd
    hh:mm:ss.xxx', ie from microsecond to millisecond resolution.

    Version 6.5 didn't have this problem, and as I mentioned, nor does any other
    database engine that our application talks to.
    ================================================== ===

    Anyone any ideas???

    Cheers,
    Geoff.

  2. #2
    zahid Guest

    Date/time problem in SQL server 7.0 (reply)

    Hi there,

    First thing is I don't know why U have taken Timestamp datatype for entering date. BCOS datetime is best for entering Data and to my knowledge Timestamp is used for some other purpose.

    Secondly if U want to enter the date in particular fashion. First Use the command SET DATE FORMAT DATAFORMAT. Where Dataformat is any avaliable format with SQL Server7.

    Try this and let me know if U get success.

    regards,
    zak
    (zahid ali khan)

    ------------
    Geoff Taylor at 6/2/99 4:02:26 AM

    Hi folks,
    I posted the following question on comp.databases.ms-sqlserver,
    bu, to my surprise got no responses. So I wondered if you good
    people might be able to help us out...
    ==================================================
    Our application communicates with SQLServer 7 through ODBC. In
    order to write date/time fields into the database, it uses either the dt
    conversion function, or more usually the ts conversion, on a string of
    the format 'yyyy-mm-dd hh:mm:ss.xxxxxx'. With almost every every
    other database engine (I think Informix is the only exception), this is
    accepted and converted into a date format that the back end can store.

    With SQLServer 7, any inserts containing dates are dropped because the
    timestamp conversion isn't working. If I take a copy of the select statement
    into the query analyser, it reports an error unless I change the timestamp
    string format from ts 'yyyy-mm-dd hh:mm:ss.xxxxxx' to ts 'yyyy-mm-dd
    hh:mm:ss.xxx', ie from microsecond to millisecond resolution.

    Version 6.5 didn't have this problem, and as I mentioned, nor does any other
    database engine that our application talks to.
    ================================================== ===

    Anyone any ideas???

    Cheers,
    Geoff.

  3. #3
    Ross McNaughton Guest

    Date/time problem in SQL server 7.0 (reply)


    The problem is that our application generates generic SQL through ODBC, so an engine-specific solution isn't very helpful. The reason we use {ts 'dd/mm/yyyy hh:mm:ss.xxxxxx'} is that it works with every other engine. What we're saying is that a piece of generic SQL which worked fine against SQLServer 6.5 is not working against v7.

    Ross McNaughton
    Cyborg CDS Ltd
    (working on this problem with Geoff)

    ------------
    zahid at 6/2/99 11:14:00 PM

    Hi there,

    First thing is I don't know why U have taken Timestamp datatype for entering date. BCOS datetime is best for entering Data and to my knowledge Timestamp is used for some other purpose.

    Secondly if U want to enter the date in particular fashion. First Use the command SET DATE FORMAT DATAFORMAT. Where Dataformat is any avaliable format with SQL Server7.

    Try this and let me know if U get success.

    regards,
    zak
    (zahid ali khan)

    ------------
    Geoff Taylor at 6/2/99 4:02:26 AM

    Hi folks,
    I posted the following question on comp.databases.ms-sqlserver,
    bu, to my surprise got no responses. So I wondered if you good
    people might be able to help us out...
    ==================================================
    Our application communicates with SQLServer 7 through ODBC. In
    order to write date/time fields into the database, it uses either the dt
    conversion function, or more usually the ts conversion, on a string of
    the format 'yyyy-mm-dd hh:mm:ss.xxxxxx'. With almost every every
    other database engine (I think Informix is the only exception), this is
    accepted and converted into a date format that the back end can store.

    With SQLServer 7, any inserts containing dates are dropped because the
    timestamp conversion isn't working. If I take a copy of the select statement
    into the query analyser, it reports an error unless I change the timestamp
    string format from ts 'yyyy-mm-dd hh:mm:ss.xxxxxx' to ts 'yyyy-mm-dd
    hh:mm:ss.xxx', ie from microsecond to millisecond resolution.

    Version 6.5 didn't have this problem, and as I mentioned, nor does any other
    database engine that our application talks to.
    ================================================== ===

    Anyone any ideas???

    Cheers,
    Geoff.

  4. #4
    Yong Li Guest

    Date/time problem in SQL server 7.0 (reply)

    I would assume that your o/s is NT. We have had the same problem with NT not Windows95. To get around it, in your ODBC setup (we have an application ini in our case), try to put in a DBParm as DateTime='''MM/DD/YYYY HH:MM:SS'' ' in additioin to the ConnectionString (comma separated).


    ------------
    Ross McNaughton at 6/3/99 4:29:40 AM


    The problem is that our application generates generic SQL through ODBC, so an engine-specific solution isn't very helpful. The reason we use {ts 'dd/mm/yyyy hh:mm:ss.xxxxxx'} is that it works with every other engine. What we're saying is that a piece of generic SQL which worked fine against SQLServer 6.5 is not working against v7.

    Ross McNaughton
    Cyborg CDS Ltd
    (working on this problem with Geoff)

    ------------
    zahid at 6/2/99 11:14:00 PM

    Hi there,

    First thing is I don't know why U have taken Timestamp datatype for entering date. BCOS datetime is best for entering Data and to my knowledge Timestamp is used for some other purpose.

    Secondly if U want to enter the date in particular fashion. First Use the command SET DATE FORMAT DATAFORMAT. Where Dataformat is any avaliable format with SQL Server7.

    Try this and let me know if U get success.

    regards,
    zak
    (zahid ali khan)

    ------------
    Geoff Taylor at 6/2/99 4:02:26 AM

    Hi folks,
    I posted the following question on comp.databases.ms-sqlserver,
    bu, to my surprise got no responses. So I wondered if you good
    people might be able to help us out...
    ==================================================
    Our application communicates with SQLServer 7 through ODBC. In
    order to write date/time fields into the database, it uses either the dt
    conversion function, or more usually the ts conversion, on a string of
    the format 'yyyy-mm-dd hh:mm:ss.xxxxxx'. With almost every every
    other database engine (I think Informix is the only exception), this is
    accepted and converted into a date format that the back end can store.

    With SQLServer 7, any inserts containing dates are dropped because the
    timestamp conversion isn't working. If I take a copy of the select statement
    into the query analyser, it reports an error unless I change the timestamp
    string format from ts 'yyyy-mm-dd hh:mm:ss.xxxxxx' to ts 'yyyy-mm-dd
    hh:mm:ss.xxx', ie from microsecond to millisecond resolution.

    Version 6.5 didn't have this problem, and as I mentioned, nor does any other
    database engine that our application talks to.
    ================================================== ===

    Anyone any ideas???

    Cheers,
    Geoff.

Posting Permissions

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