Loading ...
Sorry, an error occurred while loading the content.
 

RE: [SQLQueriesNoCode] DATETIME CONVERSION

Expand Messages
  • Jill Herrmann
    OK, I m racking my brain over this one and it may be simple (hopefully) to some of you. It runs fine in Query Analyzer but not from a web page using the
    Message 1 of 2 , Aug 13, 2003

      OK, I’m racking my brain over this one and it may be simple (hopefully) to some of you.   It runs fine in Query Analyzer but not from a web page using the stored procedure.  When I run the following code in a stored procedure I get an error message ‘the conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value’.  It was working just fine and now all of a suddent it doesn’t work and nothing in the code has changed.  Also, I have this same smalldatetime conversion in the SELECT part and if I comment out the datetime conversion in the WHERE clause it runs fine.  So why does it work in the SELECT part and not the WHERE part?   Here is the SP.

       

      SELECT    DISTINCT T01.APAA AS ACCOUNT, T01.APADA AS NAME,  

      CONVERT(SMALLDATETIME, convert(char(8), CASE WHEN T02.TRNMMA < 10 THEN '0' ELSE '' END + rtrim(convert(char(2),T02.TRNMMA)) + '/' + CASE WHEN T02.TRNDDA < 10 THEN '0' ELSE '' END + rtrim(convert(char(2),T02.TRNDDA)) + '/' + case WHEN t02.TRNYYA < 10 THEN '0' ELSE '' END + convert(char(2),T02.TRNYYA))) AS TRANDATE

      FROM         FAPP001A T01 JOIN

                            FAPP027A T02 ON T01.APAA = T02.APAA AND T01.DIVCA = T02. TDIVCA JOIN

                            FAPP002B T03 ON T01.APAA = T03.APAB AND T01.DIVCA = T03.DIVCB

      WHERE T01.APAA = CASE @Account WHEN '' THEN T01.APAA ELSE @Account END AND

                                      T01.APADA LIKE (@Name + '%') AND

                                      CONVERT(SMALLDATETIME, convert(char(8), CASE WHEN T02.TRNMMA < 10 THEN '0' ELSE '' END + rtrim(convert(char(2),T02.TRNMMA)) + '/' + CASE WHEN T02.TRNDDA < 10 THEN                                              '0' ELSE '' END + rtrim(convert(char(2),T02.TRNDDA)) + '/' + case WHEN t02.TRNYYA < 10 THEN '0'            ELSE '' END + RTRIM(convert(char(2),T02.TRNYYA)))) BETWEEN

                                                                      CASE @FromDate WHEN '' THEN CONVERT(SMALLDATETIME, convert(char(8), CASE WHEN T02.TRNMMA < 10 THEN '0' ELSE ''       END +                                                                                                                      rtrim(convert(char(2),T02.TRNMMA)) + '/' + CASE WHEN T02.TRNDDA < 10 THEN '0' ELSE '' END + rtrim(convert(char(2),T02.TRNDDA)) + '/' + case WHEN                                                                           t02.TRNYYA < 10 THEN '0' ELSE '' END + RTRIM(convert(char(2),T02.TRNYYA))))  ELSE CONVERT(SMALLDATETIME,@FromDate) END AND

                                                                      CASE @ToDate WHEN '' THEN CONVERT(SMALLDATETIME, convert(char(8), CASE WHEN T02.TRNMMA < 10 THEN '0' ELSE '' END                                                                                                                         + rtrim(convert(char(2),T02.TRNMMA)) + '/' + CASE WHEN T02.TRNDDA < 10 THEN '0' ELSE '' END + rtrim(convert(char(2),T02.TRNDDA)) + '/' + case                                                                                                 WHEN t02.TRNYYA < 10 THEN '0' ELSE '' END + RTRIM(convert(char(2),T02.TRNYYA))))  ELSE CONVERT(SMALLDATETIME,@ToDate) END

      ORDER BY TRANDATE

       

    • Sergio Coelho
      hi Jill! got a hard time to read that query... the convert statement can use 3 parameter when you convert to DateTime object the syntax is
      Message 2 of 2 , Aug 14, 2003
        hi Jill!
         
        got a hard time to read that query...
         
        the convert statement can use 3 parameter when you convert to DateTime object
        the syntax is
            Convert(DateTime,some_string,time_format)
         
        the 2 main time formats that you should use are 102 (MM-DD-YYYY) AND 103(DD-MM-YYYY)
        but i can't seem to find any time_format on your convert statements...
         
        try to use one of them
         
        also, check that your string on fields T02.TRNMMA and T02.TRNDDA and T02.TRNYYA are some
        kind of a date string. The error may be because of one of the values is not a valid date string
         
        have you check the Region Settings of SQL Server and SQL's PC?
         
        cheers
         
        Sergio Coelho Charrua
        Flesk lda
        WebDevelopment Team
        @ : scoelho@...
        url : www.flesk.com
         
        ----- Original Message -----
        Sent: Wednesday, August 13, 2003 5:05 PM
        Subject: RE: [SQLQueriesNoCode] DATETIME CONVERSION

        OK, I’m racking my brain over this one and it may be simple (hopefully) to some of you.   It runs fine in Query Analyzer but not from a web page using the stored procedure.  When I run the following code in a stored procedure I get an error message ‘the conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value’.  It was working just fine and now all of a suddent it doesn’t work and nothing in the code has changed.  Also, I have this same smalldatetime conversion in the SELECT part and if I comment out the datetime conversion in the WHERE clause it runs fine.  So why does it work in the SELECT part and not the WHERE part?   Here is the SP.

         

        SELECT    DISTINCT T01.APAA AS ACCOUNT, T01.APADA AS NAME,  

        CONVERT(SMALLDATETIME, convert(char(8), CASE WHEN T02.TRNMMA < 10 THEN '0' ELSE '' END + rtrim(convert(char(2),T02.TRNMMA)) + '/' + CASE WHEN T02.TRNDDA < 10 THEN '0' ELSE '' END + rtrim(convert(char(2),T02.TRNDDA)) + '/' + case WHEN t02.TRNYYA < 10 THEN '0' ELSE '' END + convert(char(2),T02.TRNYYA))) AS TRANDATE

        FROM         FAPP001A T01 JOIN

                              FAPP027A T02 ON T01.APAA = T02.APAA AND T01.DIVCA = T02. TDIVCA JOIN

                              FAPP002B T03 ON T01.APAA = T03.APAB AND T01.DIVCA = T03.DIVCB

        WHERE T01.APAA = CASE @Account WHEN '' THEN T01.APAA ELSE @Account END AND

                                        T01.APADA LIKE (@Name + '%') AND

                                        CONVERT(SMALLDATETIME, convert(char(8), CASE WHEN T02.TRNMMA < 10 THEN '0' ELSE '' END + rtrim(convert(char(2),T02.TRNMMA)) + '/' + CASE WHEN T02.TRNDDA < 10 THEN                                              '0' ELSE '' END + rtrim(convert(char(2),T02.TRNDDA)) + '/' + case WHEN t02.TRNYYA < 10 THEN '0'            ELSE '' END + RTRIM(convert(char(2),T02.TRNYYA)))) BETWEEN

                                                                        CASE @FromDate WHEN '' THEN CONVERT(SMALLDATETIME, convert(char(8), CASE WHEN T02.TRNMMA < 10 THEN '0' ELSE ''       END +                                                                                                                      rtrim(convert(char(2),T02.TRNMMA)) + '/' + CASE WHEN T02.TRNDDA < 10 THEN '0' ELSE '' END + rtrim(convert(char(2),T02.TRNDDA)) + '/' + case WHEN                                                                           t02.TRNYYA < 10 THEN '0' ELSE '' END + RTRIM(convert(char(2),T02.TRNYYA))))  ELSE CONVERT(SMALLDATETIME,@FromDate) END AND

                                                                        CASE @ToDate WHEN '' THEN CONVERT(SMALLDATETIME, convert(char(8), CASE WHEN T02.TRNMMA < 10 THEN '0' ELSE '' END                                                                                                                         + rtrim(convert(char(2),T02.TRNMMA)) + '/' + CASE WHEN T02.TRNDDA < 10 THEN '0' ELSE '' END + rtrim(convert(char(2),T02.TRNDDA)) + '/' + case                                                                                                 WHEN t02.TRNYYA < 10 THEN '0' ELSE '' END + RTRIM(convert(char(2),T02.TRNYYA))))  ELSE CONVERT(SMALLDATETIME,@ToDate) END

        ORDER BY TRANDATE

         



        To unsubscribe from this group, send an email to:
        SQLQueriesNoCode-unsubscribe@yahoogroups.com



        Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
      Your message has been successfully submitted and would be delivered to recipients shortly.