query result displayed differently when run across different databses on the same SQL Server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

query result displayed differently when run across different databses on the same SQL Server

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-03 : 09:54:52

Paul writes "I have two databases on a windows NT 4.0 sp6a member server running SQl Server 7.0 Standard Edition with SP2.

When I run the following script in query analyser against one database:

declare @date varchar(10)

select @date = replace(convert(varchar(10), convert(datetime, getdate()), 121), '-', '')

select @date

I get the following result:

2002 04 02

When I run the same script against the second database (which is on the same server) I get this result:

20020402

I have tried adding a second replace statement to get rid of what appear to be spaces in the result against the first database but it makes no difference. It looks as if the result is the same against each database, it's just being displayed differently. But if I try and cast or convert the first result to datatype int (which is my ultimate goal) I get this error:

Server: Msg 245, Level 16, State 1, Line 4
Syntax error converting the varchar value '2002 04 02' to a column of data type int.

However converting/casting the second result is not a problem!!!

My first thoughts were the language setting but thats at server level as far as I can make out, not database level.

I would be Soooooo.... grateful if someone was able to shed some light on this problem because I'm just out of ideas and havn't a clue whats going on."

palcon
Starting Member

6 Posts

Posted - 2002-04-03 : 10:02:02

I still can't eplain the above behaviour. But I have discoered alternative T-SQL that gets me the desired result:

declare @intdate int

select @intdate = (datepart(yyyy,getdate())*10000)+(datepart(mm,getdate())*100)+(datepart(dd,getdate()))

select @intdate

WHOOP !!

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-04-03 : 10:06:03

have a look at the SET DATEFORMAT command....

this affects the default display of dates....

I always put a SET DATEFORMAT xxx line at the top of ALL procedures/code

..many recommendations around here are to go for SET DATEFORMAT yyyymmddd.....as this is always unambiguous.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-03 : 10:13:40

Just out of curiosity, could one of the databases be set to SQL 6.5 compatability? In SQL 6.5 an empty string ('') is converted to a single space, while in 7.0 it is treated as a true empty string.

palcon
Starting Member

6 Posts

Posted - 2002-04-03 : 10:24:04

You could be onto something there, I do beleive my predecessor upgraded the database from 6.5 to 7.0.

How in the blue blazes do I discover if indeed the databse is running in 6.5 compatible mode? I would dearly love to know.

WHOOP !!

izaltsman
A custom title

1139 Posts

Posted - 2002-04-03 : 10:24:16

quote:

I still can't eplain the above behaviour.

Is the compatibility level the same for both dbs (I don't believe this should play a role, but I still think it's worth checking... See sp_dbcmptlevel in BOL).

<edit>
I should learn to refresh the page before I post... The answer was there for 10 minutes...

</edit>

Secondly, it seems to me you are doing way too many conversions there... And all to a wrong datatype... Doesn't this get you what you want?


declare @date_varchar varchar(10)   --I'm a bit confused  if you wanted int or varchar
declare @date_int int               --so I'm doing both

select  @date_varchar = convert(varchar(10), getdate(), 112)
        , @date_int = cast(convert(varchar(10), getdate(), 112) as int)

select @date_varchar, @date_int

---------------
Strong SQL Developer wanted in the Boston area. Please e-mail if interested.

Edited by - izaltsman on 04/03/2002 10:29:31

palcon
Starting Member

6 Posts

Posted - 2002-04-03 : 10:40:26

sp_dbcmptlevel has resolved the issue indeed database 1 was in 65 mode and databse 2 is in 70 mode

I love a happy ending!!

WHOOP !!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources