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

Question

Hi,

I have a view that 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:

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)

Answer 1

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.

Answer 2

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()