Post reply

convering and then comparing dates

  • June 7, 2011 at 6:05 pm

    #241825

    Hi,

    I have BIRTH DATE column in the database which is date time type

    sample birth dates are like below

    1934-01-31 00:00:00.000

    1945-11-24 00:00:00.000

    1935-02-15 00:00:00.000

    1938-10-08 00:00:00.000

    In Above dates year, date should be fixed as 2011 and 1 but month has to be incremented by one.

    example out put for the above birth date sample are

    OUTPUT has to be like beloow

    2/01/2011

    12/01/11945

    3/01/1935

    11/01/1938

    actually i need this as i have to do comparison like below

    rate_eff_date > '2/01/2011'

    can any ine please help me with this .

    Thank you so much in advance.

  • June 8, 2011 at 7:24 am

    #1335644

    you can do what you want with the dateadd/datediff functions

    In this case, one easy way to do it is to find the beginning of the month for the date in question, and then simply add one more month to it.

    With MySampleDates(TheDate)

    AS

    (

    SELECT CONVERT(datetime,'1934-01-31 00:00:00.000') UNION ALL

    SELECT '1945-11-24 00:00:00.000' UNION ALL

    SELECT '1935-02-15 00:00:00.000' UNION ALL

    SELECT '1938-10-08 00:00:00.000'

    )

    --'what you are asking is really to Add one month, then find the first day of that month.

    select

    DATEADD(mm,1,

    DATEADD(mm, DATEDIFF(mm,0,TheDate), 0)--the beginning of The month of the Birthday

    ) --adding one month to that

    FROM MySampleDates

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply