SQL Query ...... - SQL Server Forums

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 2008 Forums

SQL Server Administration (2008)

SQL Query ......

Author

Topic

sqldba20
Posting Yak Master

183 Posts

Posted - 2011-12-13 : 22:19:02

Folks:

When I run the SQL Query inside the view it works fine with no errors. When I execute the view with where condition of date (select * from vwTEST where ADate > '12/13/2011') it errors with 'Conversion failed when converting date and/or time from character string' even though the data is converted as datetime in the view. The 'cdata' column is data type varchar and stores dates. Here are all the details:

CREATE TABLE [dbo].[TEST](
[id] [int] IDENTITY(1,1) NOT NULL,
[details] [varchar](200) NULL,
[cdata] [varchar](200) NULL,
[timestamp] [datetime] NULL,
[rtype] [varchar](50) NULL
) ON [PRIMARY]

GO

insert into TEST (details,cdata,timestamp,rtype) values ('europe','emea',getdate(),'Region')
insert into TEST (details,cdata,timestamp,rtype) values ('August','Permanent','12/13/11 17:03','Area')
insert into TEST (details,cdata,timestamp,rtype) values ('8/25/2011','8/25/2011','12/13/11 17:03','Common')
insert into TEST (details,cdata,timestamp,rtype) values ('10/5/2011','10/5/2011','12/13/11 17:03','AuctionDate')
insert into TEST (details,cdata,timestamp,rtype) values ('10/12/2011','10/12/2011','12/13/11 17:03','SettleDate')
insert into TEST (details,cdata,timestamp,rtype) values (NULL,NULL,'12/13/11 17:03',NULL)
insert into TEST (details,cdata,timestamp,rtype) values ('Japan','Japan','12/13/11 17:03','Region')

GO

CREATE view [dbo].[vwTEST]
as

select r1.cdata as Region
, r2.cdata as [Reference Entity]
, cast(r3.cdata as datetime) as [DDate]
, cast(r4.cdata as datetime) as [ADate]
, cast(r5.cdata as datetime) as [SDate]
, r1.timestamp as [RDate]
from dbo.TEST r1 with (nolock)
join dbo.TEST r2 with (nolock) on r2.id = r1.id + 1
join dbo.TEST r3 with (nolock) on r3.id = r1.id + 2
join dbo.TEST r4 with (nolock) on r4.id = r1.id + 3
join dbo.TEST r5 with (nolock) on r5.id = r1.id + 4
where r1.rtype = 'Region'

Thanks !

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 23:41:07

see the reason for that and resolution here

http://visakhm.blogspot.com/2011/12/why-iso-format-is-recommended-while.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-12-14 : 09:23:11

Article fails to mention that there are other universal formats supported by sql server.

FORMATS:
'CCYYMMDD' { '2012-12-25' )
'CCYY-MM-DDTHH:MM:SS.mmm' ( '2012-12-25T07:30:03.000' )
'CCYYMMDD HH:MM:SS' ( '20121225 07:30:03' )

Are all universal and cannot be confused by language setting.

Everything else is prone to error.

Also

quote:

The 'cdata' column is data type varchar and stores dates. Here are all the details:

NO NO NO NO. Don't do that.

If the column is supposed to store a DATE, store a DATE, not a string.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources