How to covert yyyymmdd to mm/dd/yyyy

[adnancanada]

I have tried many times but not succeed, I have pick up date as text 20171020


When I use isdate function it recognize as date shows 1
I want to covert this date to 10/20/2017

Kindly help me out . Thanks in advance.

SELECT

[pbaldy]

Along the lines of:

DateSerial(Left(FieldName, 4), Mid(FieldName, 5, 2), Right(FieldName, 2))

[adnancanada]

Along the lines of:

DateSerial(Left(FieldName, 4), Mid(FieldName, 5, 2), Right(FieldName, 2))

Not-working
Error mesg: 'Mid' is not a recognized built-in function name.
it works in Excel not in SQL.

[alansidman]

Look at this video and see if you can do what you need after watching it.
http://www.datapigtechnologies.com/f...anipulate.html
Also the mid function is an Access function. Look at this link

https://www.techonthenet.com/access/...string/mid.php

[pbaldy]

Not-working
Error mesg: 'Mid' is not a recognized built-in function name.
it works in Excel not in SQL.

Works for me, in a query or in VBA:

?DateSerial(Left("20171020", 4), Mid("20171020", 5, 2), Right("20171020", 2))
10/20/2017

[June7]

This is a query built in Access query designer? Not a query in SQLServer or MySQL or T-SQL? Will Right() work - test it by itself.

[pbaldy]

Oh, I missed that this was in SQL Server forum. If in T-SQL simply:

convert(date,FieldName)

In a test:

select convert(date,'20171020')

returned

2017-10-20