0

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:

Screenshot of result from 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)

Community
  • 1
  • 1
MBaas
  • 7,248
  • 6
  • 44
  • 61
  • Your screenshot doesn't display correctly – Steve De Caux Dec 28 '09 at 11:57
  • @Steve, yeah, the screenshot didn't display right, but now it does, seems Andomar fixed it). @Andomar: Thanks! :) What did you do to make it show up? – MBaas Dec 28 '09 at 12:00
  • @MBaas: You were linking to a webpage that contained an image. I right clicked the image and copied the link (which ended in .PNG) and used that as the image link – Andomar Dec 28 '09 at 12:02

2 Answers2

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.

Andomar
  • 232,371
  • 49
  • 380
  • 404
0

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

Steve De Caux
  • 1,779
  • 12
  • 13