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

DateTime format

I'm using shortdate().
The problem is if I enter a date 01/24/2007 12:00:00 AM,
I get the result as 1/24/2007 and not 01/24/2007.
Hence when I compare dates, there will not be any resulting data for the above given date?

What should I do to get the shortdate as 01/24/2007?Shortdate... So this is an Access question and not a SQL Server question?

DateTime Error

I have a function that is based aounr the input of parameters. The last remaing issue is that I am required to enter the data into the parameter field as mm/dd/yyyy. I want to be able to enter the data as dd/mm/yyyy. I have tried to use

WHERE (CONVERT(datetime,src_terrier.datadate,103) = @.dt_src_date) AND..........

But this just throws an error "Msg 8114, Level 16, State 1, Procedure spWTRalldatareportsummary, Line 0
Error converting data type nvarchar to datetime."

The execution line I am using is

USE [DashboardSQL-2K5]
GO

DECLARE @.return_value int

EXEC @.return_value = [dbo].[spWTRalldatareportsummary]
@.dt_src_date = N'28/04/2006',
@.chr_div = NULL,
@.vch_portfolio_no = NULL,
@.vch_prop_cat = NULL

SELECT 'Return Value' = @.return_value

GO
Anybody got any ideas as to what I have done wrong? I have also tried it without the N just before the date and get a varchar version of the same error.

Thanks in advance

You should convert src_terrier.datadate not to datetime (it IS datedime, after all :) ), but to varchar. And no, you don't need N in this case.

HTH :)

|||Hi,

first of all, this is right, you don′t need to N the datestring. If you want to pass the "datestring as a string, I would rather prefer passing it as ISO date e.g. 20040101 rather than in a formatted way. Then you need to convert it to VARCHAR if you use a format / convert function. The isu code for formatting to 20040101 is 112, so this should be something like

WHERE (CONVERT(VARCHAR(10),src_terrier.datadate,112) = @.dt_src_date) AND..........

The other option you could use, which would normally better keep track of an index rather than convert all the datetimes stored in your table. Use datediff, it should be 0 if you compare by days and the record is of the same day as the imput paramter.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Is src_terrier.datadate column smalldatetime/datetime? If not you should first change it to one of the datetime data types so you can manipulate the data efficiently and perform searches in meaningful manner. As for the input, you can use the ISO unseparated format as mentioned so the code works under any language settings. If you are going to pass the value in a specific format then perform a conversion in the code like:

convert(datetime, @.dt_src_date, 101 )

convert(datetime, @.dt_src_date, 103)

If you try there and it doesn't resolve the problem then please post a sample script with DDL & data so it is easy to see what the schema looks like and the actual code.

Dates, stored procedures and asp.net

Hi,
I am having problems with dates and timestamps...

I have a textbox that allows the user to enter a date - the format is ccyymmdd. I have validation on this to ensure it is in the correct format.

I pass this as a parameter to a sql server stored procedure. In the stored procedure that parameter is defined as datetime and the field in the database it is also datetime.

To cut a long story short...an exception is being thrown! I have convinced myself it is due to the date - everything else I have used before. I am not sure whether I need to do a conversion in SQL perhaps prior to insert but not sure how to do this in any case, or, whether I have to do something in the vb code before loading the parameter??

CREATE PROCEDURE [dateexample]
(@.SomeDate [datetime])

AS INSERT INTO [testdate]
([SomeDate])

VALUES
(@.SomeDate)
GO

Anyone had similar problems / suggestions ? If this is not posted in an appropriate forum I apologise like I say I'm not sure which side the solution lies...

Thanks to all who take the time to read thistry changing your SP to this one..

CREATE PROCEDURE dateexample (@.SomeDate datetime)
AS
begin
INSERT INTO testdate VALUES (@.SomeDate)
end
GO

|||Is the exception being thrown by SQL Server, or by your vb code?|||Just to make sure, since you mention timestamps in your original post. I hope your problem isn't that you're trying to use a timestamp field to store a date/time.
A timestamp is not a date or time field. It's a serial number. The name is somewhat misleading. It's one of the unfortunate legacy things from back in the Sybase days.

From SQL Books-on-line...

timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.

Remarks
The Transact-SQL timestamp data type is not the same as the timestamp data type defined in the SQL-92 standard. The SQL-92 timestamp data type is equivalent to the Transact-SQL datetime data type.

A future release of Microsoft® SQL Server? may modify the behavior of the Transact-SQL timestamp data type to align it with the behavior defined in the standard. At that time, the current timestamp data type will be replaced with a rowversion data type.

Microsoft® SQL Server? 2000 introduces a rowversion synonym for the timestamp data type. Use rowversion instead of timestamp wherever possible in DDL statements.

Dates overlow

Dear All,
When I enter a date with the year before 1950, it is rejected with an
"overflow" message.
I'm building an application that uses dates that ranges from 1200 till 2004.
So, does any body know how to solve this problem?
Regards,
Mohamed El Wakil
Teaching Assistant
Information Systems Department,
Faculty of Computers and Information,
Cairo University - Cairo
http://mohamedelwakil.tripod.com
Please, reply to mohamed.elwakil@.omeldonia.com
SQL Server accepts date from 1753 to 9999 for the datetime datatype. If you need to go out of this
span, you need to use some other representation. A string representation is one option. A number of
integer columns (one for each element) is another. One integer column counting seconds from a
certain reference date is yet another option.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mohamed Medhat" <mohamed.elwakil @. omeldonia.com> wrote in message
news:3C5F569B-9C67-421C-802E-24D14B301044@.microsoft.com...
> Dear All,
> When I enter a date with the year before 1950, it is rejected with an
> "overflow" message.
> I'm building an application that uses dates that ranges from 1200 till 2004.
> So, does any body know how to solve this problem?
> Regards,
> Mohamed El Wakil
> Teaching Assistant
> Information Systems Department,
> Faculty of Computers and Information,
> Cairo University - Cairo
> http://mohamedelwakil.tripod.com
>
> Please, reply to mohamed.elwakil@.omeldonia.com
>
|||The exact "overflow" error would be helpful. Does it come from SQL Server
or from whatever application you are using to enter the date?
Are you using two digits for the year? I recommend that you use more...
There are easier ways to enter dates than typing them all in...a WHILE loop
might be easier!
You may have some problems entering dates earlier than 1753.
From Books Online:
datetime and smalldatetime
Date and time data types for representing date and time of day.
datetime
Date and time data from January 1, 1753 through December 31, 9999, to an
accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds
or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007
seconds, as shown in the table.
Keith
"Mohamed Medhat" <mohamed.elwakil @. omeldonia.com> wrote in message
news:3C5F569B-9C67-421C-802E-24D14B301044@.microsoft.com...
> Dear All,
> When I enter a date with the year before 1950, it is rejected with an
> "overflow" message.
> I'm building an application that uses dates that ranges from 1200 till
2004.
> So, does any body know how to solve this problem?
> Regards,
> Mohamed El Wakil
> Teaching Assistant
> Information Systems Department,
> Faculty of Computers and Information,
> Cairo University - Cairo
> http://mohamedelwakil.tripod.com
>
> Please, reply to mohamed.elwakil@.omeldonia.com
>
|||Date outside the range 1753-9999 is not accepted at datetime type. Look for
datetime and smalldatetime in BOL for more detail.
You can come up with your own definition, not as datetime, but such as a
char column, to accommodate your needs.
Quentin
"Mohamed Medhat" <mohamed.elwakil @. omeldonia.com> wrote in message
news:3C5F569B-9C67-421C-802E-24D14B301044@.microsoft.com...
> Dear All,
> When I enter a date with the year before 1950, it is rejected with an
> "overflow" message.
> I'm building an application that uses dates that ranges from 1200 till
2004.
> So, does any body know how to solve this problem?
> Regards,
> Mohamed El Wakil
> Teaching Assistant
> Information Systems Department,
> Faculty of Computers and Information,
> Cairo University - Cairo
> http://mohamedelwakil.tripod.com
>
> Please, reply to mohamed.elwakil@.omeldonia.com
>

Dates and Differences between Query Analyzer and Enterprise Manager

Hey Everyone,

I am just starting to learn T-SQL and came across something that puzzled me when looking at dates.

If I enter this:
SELECT CONVERT(datetime, CONVERT(varchar, GETDATE(), 101)) AS Expr1

In Enterprise Manager I get this output
12/30/2002 - which is what I assumed that I should get

In Query Analyzer I get this output
2002-12-30 00:00:00.000 - which I did not expect

Does anyone know why there would be a difference and if so, how do I get Query Analyzer to format as 12/31/2002?

Thanks alot,
BrentTry only: SELECT CONVERT(varchar, GETDATE(), 101) AS Expr1|||The style parameter is used when converting to a character type from a datetime - so when you converted it back to a datetime in query analyzer it showed you what you asked for. This is the true format - em will jack with the format but the reality is different. em will see all zeroes for time as no time and only displays slashes (no dashes).