Change Text to a date

I have a text field that I need to display in a data format
currently the text field is shown as this yyyymmdd
I need to display it in a date format as mm/dd/yyyy
I have tried just using the date format, but it is not recognized as a date.
Any suggestions how to write an expression to manipulate the field? I know
how to do it in Access & excel either of the folowing works:
=Mid([promise_dt],5,2) & "/" & Right([promise_dt],2) & "/" &
Left([promise_dt],4)
=(DATE(LEFT(A3,4),MID(A3,5,2),RIGHT(A3,2)))
any suggestions would be great.
thanks
--
Jeanne Conde, MCPJeanne:
Try to use SQL standard command:
declare @.InputStringDate varchar(10)
set @.InputStringDate ='20060314'
select convert(datetime, @.InputStringDate)
select convert(varchar,convert(datetime, @.InputStringDate),101)
Good luck!!
Waikiki Frog
Jeanne Conde wrote:
>I have a text field that I need to display in a data format
>currently the text field is shown as this yyyymmdd
>I need to display it in a date format as mm/dd/yyyy
>I have tried just using the date format, but it is not recognized as a date.
>Any suggestions how to write an expression to manipulate the field? I know
>how to do it in Access & excel either of the folowing works:
>=Mid([promise_dt],5,2) & "/" & Right([promise_dt],2) & "/" &
>Left([promise_dt],4)
>=(DATE(LEFT(A3,4),MID(A3,5,2),RIGHT(A3,2)))
>any suggestions would be great.
>thanks
>
--
Message posted via http://www.sqlmonster.com|||Thanks.
i will give it a try
--
Jeanne Conde, MCP
"WaikikiFrog via SQLMonster.com" wrote:
> Jeanne:
> Try to use SQL standard command:
> declare @.InputStringDate varchar(10)
> set @.InputStringDate ='20060314'
> select convert(datetime, @.InputStringDate)
> select convert(varchar,convert(datetime, @.InputStringDate),101)
> Good luck!!
> Waikiki Frog
>
> Jeanne Conde wrote:
> >I have a text field that I need to display in a data format
> >currently the text field is shown as this yyyymmdd
> >
> >I need to display it in a date format as mm/dd/yyyy
> >I have tried just using the date format, but it is not recognized as a date.
> >Any suggestions how to write an expression to manipulate the field? I know
> >how to do it in Access & excel either of the folowing works:
> >=Mid([promise_dt],5,2) & "/" & Right([promise_dt],2) & "/" &
> >Left([promise_dt],4)
> >=(DATE(LEFT(A3,4),MID(A3,5,2),RIGHT(A3,2)))
> >
> >any suggestions would be great.
> >thanks
> >
> --
> Message posted via http://www.sqlmonster.com
>