Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2007
    Posts
    29

    Unanswered: DATEPART sales month ?

    Hi,

    Im to get sales from previous month sale with this statement: but getting this error

    Cannot convert 0 to date

    Code:
    SELECT
    table1.name AS Catagory,
    COUNT(table2.sales_count) AS Qty,
    SUM(table2.sales_total) AS Total
    FROM table2 
    JOIN table1 
    ON table2.seq = table1.seq
    WHERE table2.business_date> = dateadd(mi,5,dateadd(mm,datediff(mm,0,GETDATE())-1,0))
    AND table2.business_date< dateadd(mi,5,dateadd(mm,datediff(mm,0,GETDATE()),0))
    GROUP BY table1.name
    ORDER BY table1.name ASC
    SQLCODE
    -157
    Constant
    SQLE_CONVERSION_ERROR
    SQLSTATE
    53018
    Sybase error code
    257
    ODBC 2 State
    07006
    ODBC 3 State
    07006
    Parameter 1
    The value that could not be converted.
    Parameter 2
    The name of the type for the conversion.
    Probable cause*
    You supplied to or fetched from the database an invalid value. For example, the value 12X might have been supplied where a number was required.



    Regards

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Change the 0 to an emty string i.e.
    dateadd(mi,5,dateadd(mm,datediff(mm,'',GETDATE())-1,''))
    Or to any valid date e.g.
    dateadd(mi,5,dateadd(mm,datediff(mm,'19000101',GET DATE())-1,'19000101'))

  3. #3
    Join Date
    Oct 2007
    Posts
    29
    @pdreyer

    I did try with an empty value but getting: Cannont convert to a timestamp

    Code:
    WHERE table2.business_date >= dateadd(mi,5,dateadd(mm,datediff(mm,'',GETDATE())-1,''))
    AND table2.business_date < dateadd(mi,5,dateadd(mm,datediff(mm,'',GETDATE()),''))
    Is there need to convert some values ?

    Regards

  4. #4
    Join Date
    Oct 2007
    Posts
    29
    Im getting results with this:

    Code:
    WHERE datepart(mm,table2.business_date) = datepart(mm,GETDATE())-1
    What are the major differance with my to statements ? since datepart works and dateadd doesnt.

    Regards

  5. #5
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Which Sybase database product do you use and what version?
    You are mixing mi and mm while truncating days, use mm in both.

    Tested on ASE 15.0.3
    Code:
    SELECT dateadd(mm,5,dateadd(mm,datediff(mm,'',GETDATE())-1,''))
    ,dateadd(mm,5,dateadd(mm,datediff(mm,'',GETDATE()),''))
    , dateadd(mm,5,dateadd(mm,datediff(mm,'20000101',GETDATE())-1,'20000101'))
    ,dateadd(mm,5,dateadd(mm,datediff(mm,'20000101',GETDATE()),'20000101'))
                                                                                                                 
     -------------------------- -------------------------- -------------------------- -------------------------- 
            Apr  1 2012 12:00AM        May  1 2012 12:00AM        Apr  1 2012 12:00AM        May  1 2012 12:00AM 
    
    (1 row affected)

  6. #6
    Join Date
    Oct 2007
    Posts
    29
    @pdreyer

    Thanks.

    Yes, I figured it out. Since T-SQL automatically convert 0 to 1900-01-01 00:00:00.000 I just added this to the statement.

    Im using Adaptive Server anywhere 9

    Code:
    SELECT dateadd(mm,5,dateadd(mm,datediff(mm,'1900-01-01 00:00:00.000',GETDATE())-1,'1900-01-01 00:00:00.000')),dateadd(mm,5,dateadd(mm,datediff(mm,'1900-01-01 00:00:00.000',GETDATE()),'1900-01-01 00:00:00.000'))
    Regards

Posting Permissions

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