Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2008
    Posts
    56

    Unanswered: Reformat of a date column

    Hi,
    I need to perform caculations on two date columns using datediff. The issue I have is that some of the date columns only have mm/yyyy. When this occurs I need to change the value to mm/dd/yyyy and insert 15 as the dd.

    My caculations are as follows without the required modification above.
    Any suggestions would be greatly appreciated.
    Thanks
    Rebecca

    DATEDIFF(YY, DOB, Start_DATE)
    - CASE WHEN Start_DATE >=DateAdd(yy,DateDIFF(yy,DOB,Start_DATE), DOB) THEN 0 ELSE 1
    END AS Age,

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please identify the datatypes of all the columns involved here
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2008
    Posts
    56
    Yes,
    DOB and Start_date are both varchar(70)

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you're out of luck, i'm afraid
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try this:
    Code:
    Select DOB, StartDATE,
    	year(StartDATE) 
    	- year(Dob)
    	- CASE  WHEN month(Dob) > month(StartDATE) then 1 
                WHEN month(Dob) < month(StartDATE) then 0
                WHEN day(Dob) > day(StartDATE) THEN 1 
    	    ELSE 0
    	   END as age
    from (SELECT 
    	CASE WHEN DOB like '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]' 
    			THEN CONVERT(Date, DOB, 101)
    		WHEN DOB like '[0-9][0-9]/[0-9][0-9][0-9][0-9]' 
    			THEN CONVERT(Date, '15/' + DOB, 103) 
    		ELSE NULL
    	END As dob,  
    	CASE WHEN StartDATE like '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]' 
    			THEN CONVERT(Date, StartDATE, 101)
    		WHEN StartDATE like '[0-9][0-9]/[0-9][0-9][0-9][0-9]' 
    			THEN CONVERT(Date, '15/' + StartDATE, 103) 
    		ELSE NULL
    	END AS StartDATE
    	from DaTable 
    	) as T
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •