SQL Date Conversion Query

Question

TimeStamp is a numeric datatype field in my table R for example: 2445302102010 (02/10/2010) I am trying to query by a date range of 02/09/15 - 02/15/15, the problem is I am getting results from 2010?

Select distinct
            R.CID,
            RIGHT(R.TimeStamp,8)
            from TableRev R 
            WHERE
            R.Codes in ('NY','NV') 
            AND RIGHT(R.TimeStamp,8) between 02092015 and 02152015
            ORDER BY R.TimeStamp

Answer 1

You can build the date in a way that will allow you to look for a specific date range. For that, CONCAT (or || : || can be used as a synonym for CONCAT [see Note 19]) will be helpful. So you can try something like this :

Select distinct
        R.CID,
        RIGHT(R.TimeStamp,8)
        from TableRev R 
        WHERE
        R.Codes in ('NY','NV') 
        AND (RIGHT(R.TimeStamp,4) || LEFT(RIGHT(R.TimeStamp,8),4)) 
                        between '20150209' and '20150215'
        ORDER BY R.TimeStamp