• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 227
  • Last Modified:

MSSQL Date conversion does not work in a view but does in a query


The below works fine in a query window but not in a view, any Idea why?

SELECT        CONVERT(date, CONVERT(varchar, GETDATE(), 103), 103) AS Expr1

Basically Im trying to get a view to display a datetime field as only a date in dd/mm/yyyy format.

Many thanks
1 Solution
Lee SavidgeCommented:
create view vMyView
SELECT CONVERT(varchar, GETDATE(), 103) as myDate

Then do this:
select * from vMyView
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I agree with above, and would only put VARCHAR(10) instead of just VARCHAR.
Scott PletcherSenior DBACommented:
You're likely trying to use the GUI to create the view.  Unfortunately, the GUI view-builder has many restrictions on what it can "understand".

If you instead click on the "New Query" and use a CREATE VIEW command, you can do any type of conversion you want, and you can use CASE statements (which I think the GUI also has trouble with).

The GUI can still do most of the work for you.  Put in all the columns except the one(s) you need to convert.  Create the view, script it out, then modify it to add back the converted column(s).
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now