Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Join Date
    Jun 2005
    Posts
    115

    Unanswered: problem with searching dates again

    after more than one week asking on the forum .. i get allways an erro searching on dates in MS SQL 2000

    myDate = smalldatetime

    SELECT myDate FROM dbo.myTable WHERE (myDate >= '2003-12-15') AND (myDate < '2003-12-16')


    i get the error :conversion from CHAR to SMALLMONEY out of the limits

    why smallmoney ??

    if i try

    myDate = datetime

    SELECT myDate FROM dbo.myTable WHERE (myDate >= '2003-12-15') AND (myDate < '2003-12-16')



    i get the error :conversion from CHAR to DATETIME out of the limits


    so much time and energy lost for those dates !



    thank you for helping again

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Works for me. Do you get any errors if you cut and paste and execute this code in Query Analyzer?
    Code:
    set nocount on
    
    create table #myTable1(myDate smalldatetime)
    create table #myTable2(myDate datetime)
    
    insert into #myTable1(myDate)
    select	'2003-12-14'
    UNION
    select	'2003-12-15'
    UNION
    select	'2003-12-16'
    UNION
    select	'2003-12-17'
    
    insert into #myTable2(myDate)
    select myDate from #myTable1
    
    SELECT myDate FROM dbo.#myTable1 WHERE (myDate >= '2003-12-15') AND (myDate < '2003-12-16')
    SELECT myDate FROM dbo.#myTable2 WHERE (myDate >= '2003-12-15') AND (myDate < '2003-12-16')
    
    drop table #myTable1
    drop table #myTable2
    Double check your datatypes.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jun 2005
    Posts
    115
    in my computer it works normally ...
    but in my customer's computer : problem converting CHAR in SMALLMONEY

    thanks a lot
    Last edited by quentin; 02-20-06 at 12:45.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    On your customer's QA interface, or in your customer's APPLICATION interface? There is nothing wrong with the code, so the errors is either in the data or (more likely) in the application interface.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm a bit confused by your examples. I'm assuming that:
    Quote Originally Posted by quentin
    myDate = smalldatetime

    SELECT myDate FROM dbo.myTable WHERE (myDate >= '2003-12-15') AND (myDate < '2003-12-16')
    means that you have a table named myTable with a column named myDate that has a type of smalldatetime.

    There is nothing that I can see that could be wrong with the code that you've posted. There has to be some significant difference between your example and what is causing your customer trouble. Please ask your customer to provide the SQL that they are executing (verbatim, via either cut-and-paste or sending you a .SQL file) and the output from executing:
    Code:
    EXECUTE sp_help 'dbo.myTable'
    Examine what they send you carefully, I'm sure that the problem has to lie there somewhere.

    -PatP

  6. #6
    Join Date
    Jun 2005
    Posts
    115
    BlindMan : in my customer's Query Analyser, i send to him the code to copy paste in it and he is getting this error
    the datatype is only smalldatetime nothing else

  7. #7
    Join Date
    Jun 2005
    Posts
    115
    Pat
    means that you have a table named myTable with a column named myDate that has a type of smalldatetime. : yes exactly

    the database is in french but normally no matter .. for me it works without any problem

    the code of blindman pasted in QA doesnt work for my customer in any database , even masters

    the table is very simple .. this kind :

    CREATE TABLE [dbo].[Users] (
    [id_Users] [int] NOT NULL ,
    [DateInscr] [smalldatetime] NULL ,
    [Name] [nvarchar] (100) NULL
    ) ON [PRIMARY]
    GO

    i am totally lost and if you are 2 .. then i am finish :-)

    i try EXECUTE sp_help 'dbo.myTable' ... i can save it as csv file is it right ?

    thank you
    Last edited by quentin; 02-20-06 at 15:02.

  8. #8
    Join Date
    Jun 2005
    Posts
    115
    Please ask your customer to provide the SQL that they are executing

    he is executing exactlly :

    SELECT DateInscr FROM dbo.Users WHERE ((DateInscr >= '2003-12-15') AND (DateInscr < '2003-12-16'))

    but he has this problem in any dates in any tables in any database in his SQL 2000 SERVER
    we have tried over 10 differents columns with same result
    Last edited by quentin; 02-20-06 at 15:02.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    There is nothing wrong with the code. Your customer is making any one of a hundred different stupid mistakes that could probably be found and solved in 60 seconds if you were actually at his location.

    You sent your customer MY code, and asked him to cut and paste it and execute it in his Query Analyzer EXACTLY as I wrote it, and it errors out?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What country is he in? I wonder if it has to do with either his default collation or his international settings.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Jun 2005
    Posts
    115
    Quote Originally Posted by blindman
    You sent your customer MY code, and asked him to cut and paste it and execute it in his Query Analyzer EXACTLY as I wrote it, and it errors out?
    right ! 100% right !
    i copy it on skype and we are speaking .. not by email
    he tell me what he is doing ... and he get an error at once
    and i am turning crazy !!!

    i know your code is right of course !!

    the collation is french but mine too and it works

    thanks a lot blindman .. thanks a lot to each one for your help

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Collations the same: Okay.

    What about internation settings on the server? This could cause dates to be interpreted differently.

    Try running sp_configure on both systems and compare the results to look for differences.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If Blindman's code works for you, but not for your customer, then there is something different between your test system and your customer's production system. The code that Blindman sent you is not locale dependant in any way (that is the beauty of using ISO 8601 formatted dates), so the American versus French differences are just irrelevant.

    I'm beginning to suspect that there is something seriuosly wrong with their SQL machine, either at the operating system or the SQL Server level. One way to test for that is to do a simple query against a table that has no corporeal form (it isn't on disk anywhere, it only exists within SQL Server's "mind" so to speak). Ask your customer to execute the SQL command:
    Code:
    SELECT *
       FROM master.dbo.sysprocesses
       WHERE  '1900-01-01' < last_batch
    If this returns results, then the problem lies somewhere in what your customer is doing (code, table structures, or something else) but not in the server, else if it produces an error message or returns no results then the Operating System or SQL Server is corrupt.

    -PatP

  14. #14
    Join Date
    Jun 2005
    Posts
    115
    he is trying this on 3 deiiferent databasesand get the same error , even on master
    if i run
    SELECT *
    FROM master.dbo.sysprocesses
    WHERE '1900-01-01' < last_batch
    i get 22 rows ..it works for me

    sp_configure works too for me :-) great !!


    i must wait tomorow to try it when my customer is on Skype



    thanks again
    Last edited by quentin; 02-20-06 at 16:31.

  15. #15
    Join Date
    Jun 2005
    Posts
    115
    anyway even for me it works with
    2005-12-15
    but not with
    2005-12-02
    if the day starts with a 0 i dont get any error but no rows are returned ....

    for the moment the only way to get it for me

    SELECT myDate
    FROM myTable
    WHERE
    (CONVERT(DATETIME, myDate, 120)
    BETWEEN
    CONVERT(DATETIME, '2005/12/07 00:00:00', 120)
    AND
    CONVERT(DATETIME, '2005/12/08 00:00:00', 120))



    i must solve this problem and it seems that MS SQL 2000 or 2005 doesnt work with dates normally
    Last edited by quentin; 02-21-06 at 06:50.

Posting Permissions

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