DATEPART sales month ?

mberggren · #1 (**permalink**) 12-16-11, 07:21

Hi,

I´m 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

pdreyer · #2 (**permalink**) 12-19-11, 08:52

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'))

mberggren · #3 (**permalink**) 12-20-11, 03:11

@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

mberggren · #4 (**permalink**) 12-20-11, 03:20

I´m 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 doesn´t.

Regards

pdreyer · #5 (**permalink**) 12-21-11, 08:13

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)

mberggren · #6 (**permalink**) 12-21-11, 08:35

@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.

I´m 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

Internet Services

Web Development

Digital Marketing

Consumer Tech