Convert Varchar to Date not working

Transact-SQL
1

Hello, I hope someone can help / tell me what I am doing wrong!

I have a table column which is formatted to Varchar(8) and contains date information in the format 21151103 (yyyymmdd).

I have been trying to use the following syntax in a SQl View to output this in the date format 03/11/2015.

CONVERT(Varchar(8), DateField, 103) AS DateValue

However, the output is delivered in the same format as the table i.e. 21151103

Many thanks in advance
Paul

2

Try

SELECT CONVERT(DATE,CONVERT(VARCHAR(10),DateField), 101) StartDate
3

Since 21151103 is the format SQL likes try:

declare @d varchar(8) = '21151103';
select CONVERT(VARCHAR(10),CAST(@d AS DATE),103);

Do not forget that you are adding two characters

4

Hi sz1,

Thank you for the suggestion, however I'm getting an error 'Cannot Call Methods on Date', which appears to be linked to a bug in SQL 2008 Error in view when using CONVERT(Date,GETDATE())

As it happens we are going to convert to SQL 2012 next month so unless someone comes up with an alternative option I'll have to be patient. :neutral_face:

5

Don't use the view designer create a new query and use the code, does that work?

6

Thank you djji55, that looks to have done the trick. :relaxed:

7

Try CAST(GETDATE() AS DATE)

1 Like
8 
select cast(cast(DateField as char(10)) as date)
	from table
9

HI sz1,

Same problem with Call Method on Date unfortunately.

Both options work as a query but as soon as you convert that to a view it throws a wobbly

But thank you for your time and effort. All good learning!

Paul

10

No probs, I can create a view fine and it works fine must be the bug on your version SSMS.