datetime type for debugging store procedure

Some of the pass-in parameters in my store procedure are datetime
type. I have tried to enter parameters in the debug window in the
QueryAnalyzer, but everytime it came back with error
[Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
The parameters I have tried are:
1/1/1998
'1/1/1998'
#1/1/1998#
What should I enter in the debug window for the datetime?
Thanks,
AlanTry '19980101'
Andrew J. Kelly
SQL Server MVP
"Alan" <alan_test@.yahoo.com> wrote in message
news:rdhjgvs83vclf99359l9c1sjm6d3q76c2k@.4ax.com...
> Some of the pass-in parameters in my store procedure are datetime
> type. I have tried to enter parameters in the debug window in the
> QueryAnalyzer, but everytime it came back with error
> [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
> specification
> The parameters I have tried are:
> 1/1/1998
> '1/1/1998'
> #1/1/1998#
> What should I enter in the debug window for the datetime?
> Thanks,
> Alan|||Thanks for reply, but I just tried both
'19980101'
19980101
It came back with same error message.
Alan
On Mon, 7 Jul 2003 15:24:41 -0400, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:
>Try '19980101'|||Sorry. I remember now that you must use the format as follows (Taken from
BOL):
{ ts 'yyyy-mm-dd hh:mm:ss[.fff] '} such as: { ts '1998-09-24 10:02:20' }
{ d 'yyyy-mm-dd'} such as: { d '1998-09-24' }
{ t 'hh:mm:ss'} such as: { t '10:02:20'}
This is the international way to format dates and times in ODBC and OLEDB.
--
Andrew J. Kelly
SQL Server MVP
"Alan" <alan_test@.yahoo.com> wrote in message
news:qrjjgvoiu0oj3bjon5fk7mt8l565ujevpj@.4ax.com...
> Thanks for reply, but I just tried both
> '19980101'
> 19980101
> It came back with same error message.
>
> Alan
> On Mon, 7 Jul 2003 15:24:41 -0400, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
> >Try '19980101'
>|||1998-09-24 10:02:20 works.
Thanks a lot.
Alan
On Mon, 7 Jul 2003 17:18:38 -0400, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:
>Sorry. I remember now that you must use the format as follows (Taken from
>BOL):
>{ ts 'yyyy-mm-dd hh:mm:ss[.fff] '} such as: { ts '1998-09-24 10:02:20' }
>{ d 'yyyy-mm-dd'} such as: { d '1998-09-24' }
>{ t 'hh:mm:ss'} such as: { t '10:02:20'}
>
>This is the international way to format dates and times in ODBC and OLEDB.sql

DateDiff Function, VERY IMPORTANT!!

I have 2 dates one a parameter and one from a field, I need to calculate the
number of days inside a reporting services expression window. The Datediff
function does not seem to work, I cannot do it on the SQL Query because one
of the dates is from a parameter (ie. @.date)You can use the datediff in your query.
SELECT DATEDIFF(day, pubdate, @.date) AS no_of_days
FROM titles where pubdate > @.date
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"DragonVic" <DragonVic@.discussions.microsoft.com> wrote in message
news:AF5A3BFA-C95D-4967-B226-16CE4B4A69EB@.microsoft.com...
>I have 2 dates one a parameter and one from a field, I need to calculate
>the
> number of days inside a reporting services expression window. The Datediff
> function does not seem to work, I cannot do it on the SQL Query because
> one
> of the dates is from a parameter (ie. @.date)|||You can also do it in the expression using VB datediff ie
=datediff(DateInterval.Day,Parameters!myparm.Value,Today())
I prefer doing this stuff in sql like Mike does tho...
--
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
"DragonVic" <DragonVic@.discussions.microsoft.com> wrote in message
news:AF5A3BFA-C95D-4967-B226-16CE4B4A69EB@.microsoft.com...
>I have 2 dates one a parameter and one from a field, I need to calculate
>the
> number of days inside a reporting services expression window. The Datediff
> function does not seem to work, I cannot do it on the SQL Query because
> one
> of the dates is from a parameter (ie. @.date)

DATEADD Issue

I am attempting to automate a monthend snapshot of my data, and the
following code works when I run it from a query window in SQL 2005, but it
generates and error message when I schedule it as a job. The error I get
is...
Msg 1023, Level 15, State 1, Line 10
Invalid parameter 1 specified for dateadd.
I have tried the code below using "Month", "M", 'Month', 'M', 'mm', and more
as the parameters for DATEADD, but all return the same error from job
execution. I'm sure that I'm just missing something simple, but its driving
me crazy.
IF Day(GETDATE()) = 1
BEGIN
DECLARE
@.NewName VARCHAR(12),
@.Current VARCHAR(12),
@.DataFile VARCHAR(100),
@.LogFile VARCHAR(100),
@.ThisDate DATETIME
SELECT @.ThisDate = DATEADD("Month", -1,GETDATE())
SELECT @.NewName = 'BIDW_' + CONVERT(VARCHAR(4),Year(@.ThisDate)) + '_' +
RIGHT('0' + CONVERT(VARCHAR(2),Month(@.ThisDate)),2)
SELECT @.Current = 'BIDW_' + CONVERT(VARCHAR(4),Year(GETDATE())) + '_' +
RIGHT('0' + CONVERT(VARCHAR(2),Month(GETDATE())),2)
SELECT @.DataFile = 'D:\SQLServer2005\Databases\BIDW\' + @.Current +
'_Data.mdf'
SELECT @.LogFile = 'D:\SQLServer2005\Databases\BIDW\' + @.Current +
'_Log.ldf'
EXEC sp_renamedb 'BIDW_Monthend', @.NewName
USE BIDW
RESTORE FILELISTONLY
FROM BIDW_on_Domino1
RESTORE DATABASE BIDW_Monthend
FROM BIDW_on_Domino1
WITH RECOVERY,
MOVE 'BIDW' TO @.DataFile,
MOVE 'BIDW_log' TO @.LogFile
ENDWhenever you are stuck on a problem, just post the problem to everyone in a
newsgroup and within 2 minutes of posting the question, you will realize the
answer. Nevermind everyone, I figured it out. The first part of the DATEADD
parameter needs no quotes at all.
-Brian
"Brian VanDyke" <brianv@.community.nospam> wrote in message
news:%23wcLUNSaGHA.4144@.TK2MSFTNGP04.phx.gbl...
>I am attempting to automate a monthend snapshot of my data, and the
>following code works when I run it from a query window in SQL 2005, but it
>generates and error message when I schedule it as a job. The error I get
>is...
> Msg 1023, Level 15, State 1, Line 10
> Invalid parameter 1 specified for dateadd.
> I have tried the code below using "Month", "M", 'Month', 'M', 'mm', and
> more as the parameters for DATEADD, but all return the same error from job
> execution. I'm sure that I'm just missing something simple, but its
> driving me crazy.
> IF Day(GETDATE()) = 1
> BEGIN
> DECLARE
> @.NewName VARCHAR(12),
> @.Current VARCHAR(12),
> @.DataFile VARCHAR(100),
> @.LogFile VARCHAR(100),
> @.ThisDate DATETIME
> SELECT @.ThisDate = DATEADD("Month", -1,GETDATE())
> SELECT @.NewName = 'BIDW_' + CONVERT(VARCHAR(4),Year(@.ThisDate)) + '_' +
> RIGHT('0' + CONVERT(VARCHAR(2),Month(@.ThisDate)),2)
> SELECT @.Current = 'BIDW_' + CONVERT(VARCHAR(4),Year(GETDATE())) + '_' +
> RIGHT('0' + CONVERT(VARCHAR(2),Month(GETDATE())),2)
> SELECT @.DataFile = 'D:\SQLServer2005\Databases\BIDW' + @.Current +
> '_Data.mdf'
> SELECT @.LogFile = 'D:\SQLServer2005\Databases\BIDW' + @.Current +
> '_Log.ldf'
> EXEC sp_renamedb 'BIDW_Monthend', @.NewName
> USE BIDW
> RESTORE FILELISTONLY
> FROM BIDW_on_Domino1
> RESTORE DATABASE BIDW_Monthend
> FROM BIDW_on_Domino1
> WITH RECOVERY,
> MOVE 'BIDW' TO @.DataFile,
> MOVE 'BIDW_log' TO @.LogFile
> END
>