Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6

    Answered: Unable to insert a converted date into a date column ((data type))

    PHP Code:
     INSERT INTO [GPO].dbo.tblMetric  (KPI_IDMETRIC_IDGOALREPORTING_MONTHACTUALS


          
    SELECT 
        
          1 
    AS KPI_OWNER_ID
        
    23 AS METRIC_ID 
        
    .75 AS GOAL 
        
    CAST(Z.REPORTING_MONTH as DATE) AS REPORTING_MONTH
        
    SUM(CAST(FTP_COUNT AS DECIMAL))/SUM(CAST(FULL_COUNT AS DECIMAL)) AS ACTUALS
        
      FROM  

    (

    SELECT  
          
          
         
           CONVERT
    (VARCHAR(7),CAST(A.REPORTING_MONTH '01' AS DATETIME),120) AS REPORTING_MONTH -- for 'YYYY-MM' format
           
            
          
    ,A.SUBMISSION_ID

           
    SUM(CASE C.MEDIUM_RCVD WHEN 'FTP' THEN 1 ELSE 0 END) AS FTP_COUNT 
           
          
    SUM(CASE C.MEDIUM_RCVD WHEN 'EMAIL' THEN 1 ELSE 0 END) AS EMAIL_COUNT
       
           
    ,SUM(CASE C.MEDIUM_RCVD WHEN C.MEDIUM_RCVD THEN 1 ELSE 0 END) AS FULL_COUNT
          
       
      FROM 
    [GPO].[dbo].[vw_FILE_SUBMISSION] as A
      JOIN dbo
    .DW_SUBMISSION as C
      ON C
    .SUBMISSION_ID A.SUBMISSION_ID
      GROUP BY A
    .SUBMISSION_IDA.REPORTING_MONTHA.REC_BASED_ON_TERMSC.MEDIUM_RCVD  ) AS Z
      
      JOIN 
    [GPO].[dbo].[vw_FILE_SUBMISSION] as B
      ON B
    .SUBMISSION_ID Z.SUBMISSION_ID 

      
      GROUP BY Z
    .REPORTING_MONTH
      ORDER BY REPORTING_MONTH 

    The insert column I am trying to get into is a date type. The original state of the field is YYYYMM varchar.

    Any idea how to get this into the table. UGH!

  2. Best Answer
    Posted by VLOOKUP

    "cast(SUBSTRING(A.REPORTING_MONTH,5,2) + '/01/' + SUBSTRING(A.REPORTING_MONTH,1,4) as DATE) AS REPORTING_MONTH

    This worked I wasn't bringing in the day, rookie mistake; an area I never been before. It took my co worker 3 minutes, it days like these I wish I was a project manager or account manager, staying far way from SQL. UGH.....

    FYI Frustrating!"


  3. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Based on the discussion in http://www.dbforums.com/showthread.p...ehousing-Dates I'd use the date table for this task, joining the from_yyyymm column in the date table against the [GPO].[dbo].[vw_FILE_SUBMISSION].[REPORTING_MONTH] then using the date_table.base_date to get the appropriate date value.

    You can do the same thing with an expression like what you're trying to do, it is just harder.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #3
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    cast(SUBSTRING(A.REPORTING_MONTH,5,2) + '/01/' + SUBSTRING(A.REPORTING_MONTH,1,4) as DATE) AS REPORTING_MONTH

    This worked I wasn't bringing in the day, rookie mistake; an area I never been before. It took my co worker 3 minutes, it days like these I wish I was a project manager or account manager, staying far way from SQL. UGH.....

    FYI Frustrating!
    Last edited by VLOOKUP; 08-24-15 at 18:13.

  5. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    A slightly simpler way to do that conversion as an expression that isn't locale dependant would be:
    Code:
    DECLARE @yyyymm VARCHAR(9) = '201010'
    
    SELECT @yyyymm AS Original
    ,  Cast(Stuff(@yyyymm, 5, 0, '-') + '-01' AS DATE) AS Converted
    I'd be willing to bet that:
    Code:
    INSERT INTO [GPO].dbo.tblMetric  (KPI_ID, METRIC_ID, GOAL, REPORTING_MONTH, ACTUALS)  
       SELECT  
          1 AS KPI_OWNER_ID 
    ,    23 AS METRIC_ID  
    ,     0.75 AS GOAL  
    ,     Z.base_date AS REPORTING_MONTH 
    ,     SUM(CASE WHEN 'FTP' = C.MEDIUM_RCVD THEN 1 ELSE 0 END) / Cast(Count(C.MEDIUM_RCVD) AS REAL) AS ACTUALS 
       FROM [GPO].[dbo].[vw_FILE_SUBMISSION] as A 
       JOIN dbo.DW_SUBMISSION as C 
          ON C.SUBMISSION_ID = A.SUBMISSION_ID 
       JOIN [GPO].[dbo].[vw_FILE_SUBMISSION] as B 
          ON B.SUBMISSION_ID = A.SUBMISSION_ID
       JOIN date_table AS Z
          ON Z.from_yyyymm = A.REPORTING_MONTH
       GROUP BY Z.base_date
       ORDER BY Z.base_date
    ...will produce the same results, faster, using less memory.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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