• techmarimuthu (6/25/2013)


    i have data in this format only

    Is there no way you can change it or make somebody else change it?

    techmarimuthu (6/25/2013)


    i wanna store one date so that why i have mentioned in int datatype of DOB

    If this is some sort of justification, I don't understand what you mean here.

    But, whatever be the reason it is never a good idea to use an INT datatype to store dates.

    If you use an INT data type to store dates, you might face some issues which will be really hard to solve afterwards.

    I have listed a few below

    1.You will not be able to stop anybody from entering invalid dates ( like 20100230 or 20100431 ) unless you implement some sort of constraints or triggers

    2.You cannot be sure that all dates entered are in a specific format. We have had so many questions on forums where people were scratching their heads after finding that a date column defined as VARCHAR has dates in all sorts of formats and they were not able to identify if 20100501 is 01st May or 05th January.

    Anyways, the below query should help you get the desired results

    DECLARE@startdate INT

    DECLARE@enddate INT

    SET@startdate = RIGHT(CONVERT(VARCHAR(8),CURRENT_TIMESTAMP,112),4)

    SET@enddate = RIGHT(CONVERT(VARCHAR(8),DATEADD(DAY,6,CURRENT_TIMESTAMP),112),4)

    SELECT*

    FROMmembers AS m

    WHERERIGHT(m.dob,4) BETWEEN @startdate AND @enddate


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/