current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 0 down vote favorite

I have this query below and I keep getting an error

Arithmetic overflow error converting expression to data type datetime error

when executing. I am pretty sure it has to do with DATE_ID = etc etc, because every time I put in a timestamp in their it works. Can anyone help me ?

SELECT 
    Dimension.[Every 10 minutes], 
    COUNT(*) as [Number of Transactions], 
    df.[Function Name]
FROM 
    dbo.table, Dimension, DimensionFunction df
WHERE 
    DATE_ID = DATEADD(day, -1, convert(varchar(50), GETDATE(),20))
    AND dbo.table.TIME = Dimension.Time
    AND df.FUNCTION_CODE = dbo.table.FUNCTION_CODE
    AND INTERFACE_ID = 2
    AND dbo.table.FUNCTION_CODE in ('ABX', 'BBB','DDD','EEE')
    AND TABLE.TIME BETWEEN '07:00' and '17:59'
GROUP BY
    Dimension.[Every 10 minutes], df.[Function Name]
ORDER BY
    Dimension.[Every 130 minutes], df.[Function Name]
share|improve this question
1  
Please stop using Old-style-joins, read here why: sqlblog.com/blogs/aaron_bertrand/archive/2009/10/08/… – NickyvV May 20 '14 at 13:13
2  
Why you convert the GETDATE to varchar when the DATEADD will convert it again to datetime? – Serpiton May 20 '14 at 13:17
    
what is the data type of date_id field? – Roopesh May 20 '14 at 13:23
    
DATE_ID is int and I didn't know about GETDATE re-converting it. can you help me with an easier solution? or guide me to a better way? – tamir102 May 20 '14 at 15:05
    
the query ran when I changed it into : where DATE_ID = convert(varchar(50), GETDATE() -1 ,112) thank you for helping me realize I didn't need to use DATEADD – tamir102 May 20 '14 at 15:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.