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

Hi

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
LVL 1
brasso_42Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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


Then do this:
select * from vMyView
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I agree with above, and would only put VARCHAR(10) instead of just VARCHAR.
0
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).
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.