2000 to 2005 - Conversion Issues

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

General SQL Server Forums

New to SQL Server Programming

2000 to 2005 - Conversion Issues

Author

Topic

pravin14u
Posting Yak Master

246 Posts

Posted - 2008-04-09 : 08:43:06

Hi All,

I have restored a SQL 2000 DB in a SQL 2005 server. The views which were converting a varchar to string was working fine in 2000 and in 2005 the views are giving a "error converting varchar to datetime" error.

Any ideas to rectify this issue?

Prakash.P
The secret to creativity is knowing how to hide your sources!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-09 : 08:46:33

Do you have your compatibility level set to 90 as well?

E 12°55'05.25"
N 56°04'39.16"

pravin14u
Posting Yak Master

246 Posts

Posted - 2008-04-09 : 08:47:37

Hi Peso, How do I check\do that?

Prakash.P
The secret to creativity is knowing how to hide your sources!

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-09 : 08:48:10

quote:
Originally posted by pravin14u

Hi All,

I have restored a SQL 2000 DB in a SQL 2005 server. The views which were converting a varchar to string was working fine in 2000 and in 2005 the views are giving a "error converting varchar to datetime" error.

Any ideas to rectify this issue?

Prakash.P
The secret to creativity is knowing how to hide your sources!

eh?

Em

pravin14u
Posting Yak Master

246 Posts

Posted - 2008-04-09 : 08:49:45

quote:
Originally posted by elancaster

quote:
Originally posted by pravin14u

Hi All,

I have restored a SQL 2000 DB in a SQL 2005 server. The views which were converting a varchar to string was working fine in 2000 and in 2005 the views are giving a "error converting varchar to datetime" error.

Any ideas to rectify this issue?

Prakash.P
The secret to creativity is knowing how to hide your sources!

eh?

Em

Sorry, it was "Varchar to DateTime"

Prakash.P
The secret to creativity is knowing how to hide your sources!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-09 : 09:31:08

Can you post the query that caused the error?

Madhivanan

Failing to plan is Planning to fail

pravin14u
Posting Yak Master

246 Posts

Posted - 2008-04-09 : 09:41:43

CAST(REPLACE(REPLACE(M.DAY__NAME,'M','-'),'D','-') AS DATETIME) BETWEEN DATEADD(YEAR,-1,DATEADD(DAY,-1,GETDATE())) AND DATEADD(DAY,-1,GETDATE())

Prakash.P
The secret to creativity is knowing how to hide your sources!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-09 : 09:43:57

How do m.Day__Name column values look like?

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-09 : 09:45:14

Post some sample data for the column

Madhivanan

Failing to plan is Planning to fail

pravin14u
Posting Yak Master

246 Posts

Posted - 2008-04-09 : 23:46:35

Sample Data for M.DAY__NAME
:
2004M07D16
2004M08D12
2004M08D18
2004M08D20
2004M08D25
2004M08D27
2004M09D02
2004M09D06
2004M09D07
2004M09D08
2004M09D09

I also tried the following:

....where IsDate( REPLACE(REPLACE(M.DAY__NAME,'M','-'),'D','-'))<>1

and the above one didnt return any rows!

Peso - I have changed my compability level to 90!

Prakash.P
The secret to creativity is knowing how to hide your sources!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 01:02:28

CAST(REPLACE(REPLACE(M.DAY__NAME, 'M', ''), 'D', '') AS DATETIME) BETWEEN DATEADD(YEAR, -1, DATEADD(DAY, -1, GETDATE())) AND DATEADD(DAY, -1, GETDATE())

E 12°55'05.25"
N 56°04'39.16"

pravin14u
Posting Yak Master

246 Posts

Posted - 2008-04-10 : 02:17:33

Hi Peso,

I am getting the same error again!!!

And Can you please explain the chnage that you are trying to do?

Prakash.P
The secret to creativity is knowing how to hide your sources!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-10 : 03:24:04

select data from
(
select '2004M07D16' as data
union all
select '62004M07D16'
) as t
where isdate(replace(replace(data,'M',''),'D',''))<>1

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources