开发者

SQL Server 2005: converting smalldatetime to varchar behaves differently in view and 'normal' query

I have a view th开发者_StackOverflow中文版at basically just returns all records from a table, and adds a column 'isodate' which is supposed to be the date in ISO-Format.

CREATE VIEW [dbo].[v_bedarfe]
AS 
   SELECT *,convert(varchar(16),datum,20) As isodat FROM bedarfe
GO

The "datum"-field is smalldatetime. The results of a query on isodat were...'surprising', so to make the point clear, I tried this:

select top 10 datum,isodat,convert(varchar(16),datum,20) As isodat2 from v_bedarfe

which led to:

SQL Server 2005: converting smalldatetime to varchar behaves differently in view and 'normal' query

and that looks very wrong.

So I assume I have wrong expectations or am 'abusing' something here, but I don't see what I could be doing wrong and would appreciate any suggestions how to get back on track here...

Thanks

Michael

(hope the screenshot will display correctly when posting this, preview doesn't show it)


Using * in views is dangerous. If the table definition changes, * can cause the view to map the columns wrong. Drop & recreate the view without *, and see if that fixes the problem.

P.S. Convert 20 is actually ODBC canonical, yyyy-mm-dd hh:mi:ss(24h), see the MSDN page.


The last parm in convert is the output format.

20 => yyyy-mm-dd hh:mi:ss(24h)

131 => dd/mm/yy hh:mi:ss:mmmAM

see MS SQL convert()

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜