UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Date Conversion Error, SQL Server 2012    
 
   
bshfdan
post Oct 27 2017, 08:05 AM
Post#1



Posts: 64
Joined: 17-March 04



I'm using the following function to add the field duration_x (this is an interger field containing the number of months) to prep_step_date (date/time) to get the field next_step_date (date/time)

dateadd(m,[duration_x],[prep_step_date]) as next_step_date

This is the error I get:

MSG 257,Level 16, State 3, Line 1
Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.

I'm not sure how to get the convert statement in the dateadd function.

Thanks
Go to the top of the page
 
Doug Steele
post Oct 27 2017, 08:42 AM
Post#2


UtterAccess VIP
Posts: 21,498
Joined: 8-January 07
From: St. Catharines, ON (Canada)


The error message seems to be implying that [duration_x] is a datetime field, not an integer.

See whether this works:

CODE
dateadd(m, cast([duration_x] as int), [prep_step_date]) as next_step_date

--------------------
Go to the top of the page
 
cheekybuddha
post Oct 27 2017, 08:44 AM
Post#3


UtterAccess VIP
Posts: 9,277
Joined: 6-December 03
From: Telegraph Hill


Please post the whole SP.

There is nothing obviously wrong with that call of the DateAdd() function, so there must a type conversion issue being missed somewhere

--------------------


Regards,

David Marten
Go to the top of the page
 
bshfdan
post Oct 27 2017, 08:54 AM
Post#4



Posts: 64
Joined: 17-March 04



Here is the whole SP

INSERT INTO dbo.dbo_tbl_emp_increments_payplan
SELECT prep_emp,prem_lname,prem_fname,prem_p_bargain,prem_hire,prep_step,Job_Class,prep
_step_date,duration_x, dateadd(m,[duration_x],[prep_step_date]) as next_step_date,
[Max steps],iif([prep_step]=[Max Steps],'Max','Not at Max') as NotAtMax,left([prem_p_org],3) as dept
FROM [yvwlnmu9861cs02.tylertech.com].mu9861.dbo.premppay
inner join dbo.tbl_max_steps on [prep_bgnu] = [Bargain_Unit] collate SQL_Latin1_General_CP1_CI_AS
and [prep_grade] = [Job_Class] collate SQL_Latin1_General_CP1_CI_AS
inner join [yvwlnmu9861cs02.tylertech.com].mu9861.dbo.prempmst on [prep_emp] = [prem_emp] and [prep_proj] = [prem_proj]
inner join dbo.tbl_jobclass_2 on [prep_bgnu] = [pmgs_bargain] collate SQL_Latin1_General_CP1_CI_AS and [prep_step] = [pmgs_step]
and [prep_grade] = [pmgs_grade] collate SQL_Latin1_General_CP1_CI_AS and [prep_proj] = [pmgs_proj]

where ((([Job_Class] not like '%P') and ([duration_x] <> 0) and ([prem_act_stat] = 'A') and [prep_proj] = 0))
Go to the top of the page
 
cheekybuddha
post Oct 27 2017, 09:10 AM
Post#5


UtterAccess VIP
Posts: 9,277
Joined: 6-December 03
From: Telegraph Hill


List the fields and datatypes of dbo_tbl_emp_increments_payplan.

I suspect you've mis-matched the fields you're selecting to the fields being inserted into.

--------------------


Regards,

David Marten
Go to the top of the page
 
bshfdan
post Oct 27 2017, 09:25 AM
Post#6



Posts: 64
Joined: 17-March 04



I've attached a pdf of the table layouy. Thank you for your help
Attached File(s)
Attached File  emp_inc.pdf ( 47.81K )Number of downloads: 1
 
Go to the top of the page
 
cheekybuddha
post Oct 27 2017, 09:32 AM
Post#7


UtterAccess VIP
Posts: 9,277
Joined: 6-December 03
From: Telegraph Hill


This is your SQL statement:
CODE
INSERT INTO dbo.dbo_tbl_emp_increments_payplan
  SELECT
    prep_emp,
    prem_lname,
    prem_fname,
    prem_p_bargain,
    prem_hire,
    prep_step,
    Job_Class,
    prep_step_date,
    duration_x,
    dateadd(m,[duration_x],[prep_step_date]) as next_step_date,
    [Max steps],
    iif([prep_step]=[Max Steps],'Max','Not at Max') as NotAtMax,
    left([prem_p_org],3) as dept
  FROM [yvwlnmu9861cs02.tylertech.com].mu9861.dbo.premppay
  inner join dbo.tbl_max_steps
          on [prep_bgnu] = [Bargain_Unit] collate SQL_Latin1_General_CP1_CI_AS
         and [prep_grade] = [Job_Class] collate SQL_Latin1_General_CP1_CI_AS
  inner join [yvwlnmu9861cs02.tylertech.com].mu9861.dbo.prempmst
          on [prep_emp] = [prem_emp]
         and [prep_proj] = [prem_proj]
  inner join dbo.tbl_jobclass_2
          on [prep_bgnu] = [pmgs_bargain] collate SQL_Latin1_General_CP1_CI_AS
         and [prep_step] = [pmgs_step]
         and [prep_grade] = [pmgs_grade] collate SQL_Latin1_General_CP1_CI_AS
         and [prep_proj] = [pmgs_proj]
  where ((([Job_Class] not like '%P')
    and ([duration_x] <> 0)
    and ([prem_act_stat] = 'A')
    and [prep_proj] = 0))


I think you have Job_Class out of step. In the screenshot you posted Job_Class comes before prep_step. But then, what do you want to do with prem_hire?

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Oct 27 2017, 09:35 AM
Post#8


UtterAccess VIP
Posts: 9,277
Joined: 6-December 03
From: Telegraph Hill


Oh, I see prem_hire in the destination table.

Try:
CODE
INSERT INTO dbo.dbo_tbl_emp_increments_payplan
  SELECT
    prep_emp,
    prem_lname,
    prem_fname,
    prem_p_bargain,
    Job_Class,
    prep_step,
    prep_step_date,
    duration_x,
    dateadd(m,[duration_x],[prep_step_date]) as next_step_date,
    [Max steps],
    iif([prep_step]=[Max Steps],'Max','Not at Max') as NotAtMax,
    prem_hire,
    left([prem_p_org],3) as dept
  FROM [yvwlnmu9861cs02.tylertech.com].mu9861.dbo.premppay
  inner join dbo.tbl_max_steps
          on [prep_bgnu] = [Bargain_Unit] collate SQL_Latin1_General_CP1_CI_AS
         and [prep_grade] = [Job_Class] collate SQL_Latin1_General_CP1_CI_AS
  inner join [yvwlnmu9861cs02.tylertech.com].mu9861.dbo.prempmst
          on [prep_emp] = [prem_emp]
         and [prep_proj] = [prem_proj]
  inner join dbo.tbl_jobclass_2
          on [prep_bgnu] = [pmgs_bargain] collate SQL_Latin1_General_CP1_CI_AS
         and [prep_step] = [pmgs_step]
         and [prep_grade] = [pmgs_grade] collate SQL_Latin1_General_CP1_CI_AS
         and [prep_proj] = [pmgs_proj]
  where ((([Job_Class] not like '%P')
    and ([duration_x] <> 0)
    and ([prem_act_stat] = 'A')
    and [prep_proj] = 0))


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
bshfdan
post Oct 30 2017, 05:26 AM
Post#9



Posts: 64
Joined: 17-March 04



Thank you for your help. I corrected the sequence and it worked.
Go to the top of the page
 
cheekybuddha
post Oct 30 2017, 05:48 AM
Post#10


UtterAccess VIP
Posts: 9,277
Joined: 6-December 03
From: Telegraph Hill


yw.gif

I'm glad you worked it out!

Just to note: if you wish to pas in the values out of sequence, or you you only want to pass in values for specific fields, but not all, then you can specify the field list to inserted into:
(just for example)
CODE
INSERT INTO dbo.dbo_tbl_emp_increments_payplan (
  duration_x,
  prep_step,
  Job_Class
)
  SELECT
    duration_x,
    prep_step,
    Job_Class
  FROM [yvwlnmu9861cs02.tylertech.com].mu9861.dbo.premppay
  inner join dbo.tbl_max_steps
          on [prep_bgnu] = [Bargain_Unit] collate SQL_Latin1_General_CP1_CI_AS
         and [prep_grade] = [Job_Class] collate SQL_Latin1_General_CP1_CI_AS
  inner join [yvwlnmu9861cs02.tylertech.com].mu9861.dbo.prempmst
          on [prep_emp] = [prem_emp]
         and [prep_proj] = [prem_proj]
  inner join dbo.tbl_jobclass_2
          on [prep_bgnu] = [pmgs_bargain] collate SQL_Latin1_General_CP1_CI_AS
         and [prep_step] = [pmgs_step]
         and [prep_grade] = [pmgs_grade] collate SQL_Latin1_General_CP1_CI_AS
         and [prep_proj] = [pmgs_proj]
  where ((([Job_Class] not like '%P')
    and ([duration_x] <> 0)
    and ([prem_act_stat] = 'A')
    and [prep_proj] = 0))


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th December 2017 - 11:29 PM