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:
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()
精彩评论