Date problem

I have a table that has two datetime columns and the following stored
procedure:
create proc sp_BTInsertInitialValues
@.BankID nchar(3),
@.Librfacr nvarchar(3),
@.Modtrack nvarchar(3),
@.Fix_init_rate nvarchar(50),
@.Fix_init_match nvarchar(50),
@.Impldate nvarchar(10),
@.Implend nvarchar(10)
as
declare @.sql nvarchar(255)
set @.sql = N'insert into InitialValues values(''' + @.BankID + N''', ' +
@.Librfacr + N', ' + @.Modtrack +
N', ''' + @.Fix_init_rate + N''', ''' + @.Fix_init_match + N''', ' +
@.Impldate + N', ' + @.Implend + N')'
exec sp_executesql @.sql
go
When I execute this:
exec sp_BTInsertInitialValues 'RPT', '2', '4', 'TRES', 'TEST', '6/1/2006',
'6/30/2006'
I do not get any errors, but the two date columns have 1/1/1900 instead of
the dates in quotes. Any Suggestions?
ThanksFirst, see what triggers are on that table that might be overriding the
dates you're inserting.
Second, in your procedure, comment out the line:
exec sp_executesql @.sql
Replace it with:
print @.sql
Run the exec statement that you provided:
exec sp_BTInsertInitialValues 'RPT', '2', '4', 'TRES', 'TEST',
'6/1/2006', '6/30/2006'
Review the INSERT statement that is displayed, copy/paste it into QA
and run it.|||The datepart 1/6/2006 is evaluated to a integer division will will
result in zero, this is entered in the datetime column which is ther 0
day of the datetime column = 1/1/1900.
You should doublequote this to achieve the insert in the column,
something like this + '' + @.Variable + ''.
HTH, jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||Try this stored procedure:
alter proc sp_BTInsertInitialValues
@.BankID nchar(3),
@.Librfacr nvarchar(3),
@.Modtrack nvarchar(3),
@.Fix_init_rate nvarchar(50),
@.Fix_init_match nvarchar(50),
@.Impldate nvarchar(10),
@.Implend nvarchar(10)
as
declare @.sql nvarchar(255)
set @.sql = N'insert into InitialValues values(''' + @.BankID + N''', ' +
@.Librfacr + N', ' + @.Modtrack +
N', ''' + @.Fix_init_rate + N''', ''' + @.Fix_init_match + N''', ''' +
@.Impldate + N''', ''' + @.Implend + N''')'
print @.sql
go
I think you are missing a quote for the dates.
Lucas
"DXC" wrote:
> I have a table that has two datetime columns and the following stored
> procedure:
> create proc sp_BTInsertInitialValues
> @.BankID nchar(3),
> @.Librfacr nvarchar(3),
> @.Modtrack nvarchar(3),
> @.Fix_init_rate nvarchar(50),
> @.Fix_init_match nvarchar(50),
> @.Impldate nvarchar(10),
> @.Implend nvarchar(10)
> as
> declare @.sql nvarchar(255)
> set @.sql = N'insert into InitialValues values(''' + @.BankID + N''', ' +
> @.Librfacr + N', ' + @.Modtrack +
> N', ''' + @.Fix_init_rate + N''', ''' + @.Fix_init_match + N''', ' +
> @.Impldate + N', ' + @.Implend + N')'
> exec sp_executesql @.sql
> go
> When I execute this:
> exec sp_BTInsertInitialValues 'RPT', '2', '4', 'TRES', 'TEST', '6/1/2006',
> '6/30/2006'
> I do not get any errors, but the two date columns have 1/1/1900 instead of
> the dates in quotes. Any Suggestions?
> Thanks|||Thank you All.........
"DXC" wrote:
> I have a table that has two datetime columns and the following stored
> procedure:
> create proc sp_BTInsertInitialValues
> @.BankID nchar(3),
> @.Librfacr nvarchar(3),
> @.Modtrack nvarchar(3),
> @.Fix_init_rate nvarchar(50),
> @.Fix_init_match nvarchar(50),
> @.Impldate nvarchar(10),
> @.Implend nvarchar(10)
> as
> declare @.sql nvarchar(255)
> set @.sql = N'insert into InitialValues values(''' + @.BankID + N''', ' +
> @.Librfacr + N', ' + @.Modtrack +
> N', ''' + @.Fix_init_rate + N''', ''' + @.Fix_init_match + N''', ' +
> @.Impldate + N', ' + @.Implend + N')'
> exec sp_executesql @.sql
> go
> When I execute this:
> exec sp_BTInsertInitialValues 'RPT', '2', '4', 'TRES', 'TEST', '6/1/2006',
> '6/30/2006'
> I do not get any errors, but the two date columns have 1/1/1900 instead of
> the dates in quotes. Any Suggestions?
> Thanks