CODEWITHSUNDEEP
sqlsql-servert-sql
joe
joe

Reputation: 1473

Convert Varchar columns to Datetime

I am tasked to calculate the difference in months and fiscal years from a NUMBERS column and a SHIP_DATES column. I have trouble converting date or time from character string in the NUMBERS columns (NUMBER_MONTH), and (NUMBER_FY) before the calculations.

Could someone please look? Basically, the issue I have is I cant take SHIP_DATE_MONTH minus NUMBER_MONTH and SHIP_DATE_FY minus NUMBER_FY.

Here is my code:

CREATE TABLE #TEMP
(
  NUMBERS    VARCHAR (20),
  SHIP_DATES DATETIME,
  ID         INT
)

INSERT INTO #TEMP VALUES ( 'ABC1006000046' , '6/5/2010'  , '123' )
INSERT INTO #TEMP VALUES ( 'ABC1006000046' , '7/15/2013' , '123' )
INSERT INTO #TEMP VALUES ( 'CDS0809000059' , '9/8/2008'  , '124' )
INSERT INTO #TEMP VALUES ( 'CDS0809000059' , '1/31/2013' , '124' )

SELECT SUBSTRING(NUMBERS, 6, 2)     AS NUMBER_MONTH,
       SUBSTRING(NUMBERS, 4, 2)     AS NUMBER_FY,
       DATEPART (MONTH, SHIP_DATES) AS SHIP_DATE_MONTH,
       DATEPART (YEAR, SHIP_DATES)  AS SHIP_DATE_FY,
       ID
INTO #TEMP1
FROM #TEMP

--calculate the difference in month and fiscal year
SELECT DATEDIFF( YEAR  , NUMBER_FY    , SHIP_DATE_FY    ) AS DIFF_YEAR  ,
       DATEDIFF( MONTH , NUMBER_MONTH , SHIP_DATE_MONTH ) AS DIFF_MONTH ,
       ID
FROM #TEMP1

and this is the error message I receive:

Conversion failed when converting date and/or time from character string.

Please note, here is a converting to datetime approach, but i have not figured out how to make it work yet:

CONVERT(VARCHAR , SHIP_DATES , 101 ) AS 'MM/DD/YYYY'

Upvotes: 0

Views: 1348

Answers (3)

asafrob
asafrob

Reputation: 1858

When you call DATEDIFF the 2nd and 3rd arguments are of type DateTime where you pass strings for the 2nd argument and integers for the 3rd argument.

I think (if i understand what you are trying to do) you should convert the strings to numbers, get rid of the DATEDIFF and do a regular minus operation.

Addition after comment: to cast a nvarcahr to an int you can change your code like this:

SELECT 
    CAST(SUBSTRING(NUMBERS, 6, 2) AS BIGINT)    AS NUMBER_MONTH,
    CAST(SUBSTRING(NUMBERS, 4, 2) AS BIGINT)    AS NUMBER_FY,
    DATEPART (MONTH, SHIP_DATES) AS SHIP_DATE_MONTH,
    DATEPART (YEAR, SHIP_DATES)  AS SHIP_DATE_FY,
        ID
INTO #TEMP1
FROM #TEMP

Upvotes: 1

Ash8087
Ash8087

Reputation: 701

Why not do this?

CREATE TABLE #TEMP
(
  NUMBERS    VARCHAR (20),
  SHIP_DATES DATETIME,
  ID         INT
)

INSERT INTO #TEMP VALUES ( 'ABC1006000046' , '6/5/2010'  , '123' )
INSERT INTO #TEMP VALUES ( 'ABC1006000046' , '7/15/2013' , '123' )
INSERT INTO #TEMP VALUES ( 'CDS0809000059' , '9/8/2008'  , '124' )
INSERT INTO #TEMP VALUES ( 'CDS0809000059' , '1/31/2013' , '124' )

SELECT  CAST('20' + SUBSTRING(NUMBERS, 6, 2) + '-' + SUBSTRING(NUMBERS, 4, 2) + '-01'     AS DATE) AS NumsDate,
SHIP_DATES
INTO #TEMP2      
FROM #TEMP

SELECT 
    DATEDIFF(M, NumsDate, SHIP_DATES) AS Diff_In_Months,
    DATEDIFF(YEAR, NumsDate, SHIP_DATES) AS Diff_In_Years  
FROM 
    #TEMP2

Upvotes: 0

BWS
BWS

Reputation: 3846

I would try something more along these lines:

CREATE TABLE #TEMP 
    (NUMBERS VARCHAR (20), SHIP_DATES_A DATETIME, SHIP_DATES_B DATETIME, ID INT)

Then for testing:

INSERT INTO TEMP VALUES ('ABC1006000046', '6/5/2010','7/15/2013', '123')
INSERT INTO TEMP VALUES ('CDS0809000059', '9/8/2008','1/31/2013', '124')

Then this query:

select NUMBERS,SHIP_DATES_A,SHIP_DATES_B,ID,
    DATEDIFF(month,ship_dates_a,ship_dates_b)
from temp

Upvotes: 0

Related Questions