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
Showing posts with label proc. Show all posts
Showing posts with label proc. Show all posts
Sunday, March 25, 2012
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
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
Thursday, March 22, 2012
Date parameter
hi and thanx in advance
in a report, i am getting data from a stored proc which take to date parameter fromdate and todate which are passed from report.
user select dates in datepicker control
but problem is that it not returning any record
Can problem be in date format difference in report and sql server ?
in a report, i am getting data from a stored proc which take to date parameter fromdate and todate which are passed from report.
user select dates in datepicker control
but problem is that it not returning any record
Can problem be in date format difference in report and sql server ?
If date format difference is true, it will give error at some point. So, the problem could be only with your stored procedure. Try executing the stored procedure from the management studio with the same parameter data and see if you get any records.
Shyam
Friday, February 17, 2012
date comparison and filtering
Im creating a stored proc where i need to filter an Orders table
results based on a date field as following:
The column i'm filtering is called DeliveryDate
How to I filter DeliveryDate for This W
, This Month.
This W
:
Do I have to calculate the start of w
and end of w
and then use
a between #start# and #end#? How is this done.
This Month:
Do I have to calculate the start of month and end of month and then use
a between #start# and #end#
Does SQL provide specialized date functions for this?
Thank YouAre youo sure you mean "filter" A Filter is something that block some
records and lets some records through, based on a predicate.
If you actually want to filter the records, you use a Where Clause
Select * From TableName
Where DeliveryDate >= <StartDate> And DeliveryDate <EndDate>
To filter for only thos month, you can use the following expressions:
Foor the StartDate:
Convert(VarChar(6), getDate(), 112) + '01'
And for endDate, use:
Convert(VarChar(6), DateAdd(month, 1, getDate(), 112) + '01'
To filter for the current w
, you have to decide what day of the w
you
want to use... And set a Server-wide setting called DateFirst. Set it to a
value = 1 for Monday, 2 for Tuesday, etc.
Set DateFirst 1 -- (to make Monday the first day of the w
)
And then use these expressions:
StartDate:
convert(varChar(8), dateadd(day, 1-datepart(dw, getdate()),
getdate()), 112),
and EndDate:
convert(varChar(8), Dateadd(day, 8-datepart(dw, getdate()),
getdate()), 112)
"Opa" wrote:
> Im creating a stored proc where i need to filter an Orders table
> results based on a date field as following:
> The column i'm filtering is called DeliveryDate
> How to I filter DeliveryDate for This W
, This Month.
> This W
:
> Do I have to calculate the start of w
and end of w
and then use
> a between #start# and #end#? How is this done.
> This Month:
> Do I have to calculate the start of month and end of month and then use
> a between #start# and #end#
> Does SQL provide specialized date functions for this?
> Thank You
>
>
>
>
>|||Thanks a lot.
I will try your suggestions.
"CBretana" wrote:
> Are youo sure you mean "filter" A Filter is something that block some
> records and lets some records through, based on a predicate.
> If you actually want to filter the records, you use a Where Clause
> Select * From TableName
> Where DeliveryDate >= <StartDate> And DeliveryDate <EndDate>
> To filter for only thos month, you can use the following expressions:
> Foor the StartDate:
> Convert(VarChar(6), getDate(), 112) + '01'
> And for endDate, use:
> Convert(VarChar(6), DateAdd(month, 1, getDate(), 112) + '01'
>
> To filter for the current w
, you have to decide what day of the w
yo
u
> want to use... And set a Server-wide setting called DateFirst. Set it to
a
> value = 1 for Monday, 2 for Tuesday, etc.
> Set DateFirst 1 -- (to make Monday the first day of the w
)
> And then use these expressions:
> StartDate:
> convert(varChar(8), dateadd(day, 1-datepart(dw, getdate()),
> getdate()), 112),
> and EndDate:
> convert(varChar(8), Dateadd(day, 8-datepart(dw, getdate()),
> getdate()), 112)
> "Opa" wrote:
>|||Another way you can do this
for this month
where Datepart(mm,deliverydate) = datepart(mm,getdate())
for this w
where datepart(wk,deliverydate) = datepart(wk,getdate())
performance may not be the best when you use functions of any type in the
where clause
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Opa" <Opa@.discussions.microsoft.com> wrote in message
news:98449239-878D-41E4-8DC6-57CB5C43AF6D@.microsoft.com...
> Im creating a stored proc where i need to filter an Orders table
> results based on a date field as following:
> The column i'm filtering is called DeliveryDate
> How to I filter DeliveryDate for This W
, This Month.
> This W
:
> Do I have to calculate the start of w
and end of w
and then use
> a between #start# and #end#? How is this done.
> This Month:
> Do I have to calculate the start of month and end of month and then use
> a between #start# and #end#
> Does SQL provide specialized date functions for this?
> Thank You
>
>
>
>
>
results based on a date field as following:
The column i'm filtering is called DeliveryDate
How to I filter DeliveryDate for This W
This W
Do I have to calculate the start of w
a between #start# and #end#? How is this done.
This Month:
Do I have to calculate the start of month and end of month and then use
a between #start# and #end#
Does SQL provide specialized date functions for this?
Thank YouAre youo sure you mean "filter" A Filter is something that block some
records and lets some records through, based on a predicate.
If you actually want to filter the records, you use a Where Clause
Select * From TableName
Where DeliveryDate >= <StartDate> And DeliveryDate <EndDate>
To filter for only thos month, you can use the following expressions:
Foor the StartDate:
Convert(VarChar(6), getDate(), 112) + '01'
And for endDate, use:
Convert(VarChar(6), DateAdd(month, 1, getDate(), 112) + '01'
To filter for the current w
want to use... And set a Server-wide setting called DateFirst. Set it to a
value = 1 for Monday, 2 for Tuesday, etc.
Set DateFirst 1 -- (to make Monday the first day of the w
And then use these expressions:
StartDate:
convert(varChar(8), dateadd(day, 1-datepart(dw, getdate()),
getdate()), 112),
and EndDate:
convert(varChar(8), Dateadd(day, 8-datepart(dw, getdate()),
getdate()), 112)
"Opa" wrote:
> Im creating a stored proc where i need to filter an Orders table
> results based on a date field as following:
> The column i'm filtering is called DeliveryDate
> How to I filter DeliveryDate for This W
> This W
> Do I have to calculate the start of w
> a between #start# and #end#? How is this done.
> This Month:
> Do I have to calculate the start of month and end of month and then use
> a between #start# and #end#
> Does SQL provide specialized date functions for this?
> Thank You
>
>
>
>
>|||Thanks a lot.
I will try your suggestions.
"CBretana" wrote:
> Are youo sure you mean "filter" A Filter is something that block some
> records and lets some records through, based on a predicate.
> If you actually want to filter the records, you use a Where Clause
> Select * From TableName
> Where DeliveryDate >= <StartDate> And DeliveryDate <EndDate>
> To filter for only thos month, you can use the following expressions:
> Foor the StartDate:
> Convert(VarChar(6), getDate(), 112) + '01'
> And for endDate, use:
> Convert(VarChar(6), DateAdd(month, 1, getDate(), 112) + '01'
>
> To filter for the current w
u
> want to use... And set a Server-wide setting called DateFirst. Set it to
a
> value = 1 for Monday, 2 for Tuesday, etc.
> Set DateFirst 1 -- (to make Monday the first day of the w
> And then use these expressions:
> StartDate:
> convert(varChar(8), dateadd(day, 1-datepart(dw, getdate()),
> getdate()), 112),
> and EndDate:
> convert(varChar(8), Dateadd(day, 8-datepart(dw, getdate()),
> getdate()), 112)
> "Opa" wrote:
>|||Another way you can do this
for this month
where Datepart(mm,deliverydate) = datepart(mm,getdate())
for this w
where datepart(wk,deliverydate) = datepart(wk,getdate())
performance may not be the best when you use functions of any type in the
where clause
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Opa" <Opa@.discussions.microsoft.com> wrote in message
news:98449239-878D-41E4-8DC6-57CB5C43AF6D@.microsoft.com...
> Im creating a stored proc where i need to filter an Orders table
> results based on a date field as following:
> The column i'm filtering is called DeliveryDate
> How to I filter DeliveryDate for This W
> This W
> Do I have to calculate the start of w
> a between #start# and #end#? How is this done.
> This Month:
> Do I have to calculate the start of month and end of month and then use
> a between #start# and #end#
> Does SQL provide specialized date functions for this?
> Thank You
>
>
>
>
>
Subscribe to:
Posts (Atom)