current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
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 20 hours ago
2  
Why you convert the GETDATE to varchar when the DATEADD will convert it again to datetime? –  Serpiton 20 hours ago
    
what is the data type of date_id field? –  Roopesh 20 hours ago
    
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 18 hours ago
    
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 18 hours ago
add comment

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.