Convert Text to Date Format (TO_DATE)

Ranjit9 used Ask the Experts™
Hi all,
I have the similar kind of problem as in the question asked a from someone "Convert Text to Date Format (TO_DATE)"
What I am trying to do is I have a SQL table connected to a Access DB.I generate views from these table , I am having a problem in one of the coloumns which is a string(varchar) data type in the underlying SQL(MS) table.When presenting a view for this table I want to convert it this Varchar col to  Date (or format it as a Date field).
I almost tried all sql functionts to convert/format this col.
Here ia an example of my SQL statement
1)SELECT CDate([TimeWritten]) AS mydate, *
FROM dbo.tlbMyTable

2)SELECT format(TimeWritten,"dd/yyyy") AS mydate, *
FROM dbo.tlbEventViwer
when I excute the SQL in Access I get this Error ADO ERROR:"CDATE : Is NOT A recognised function blah , blah"

How can I reslove this problem.
Any help plsss??
Thank you in advance.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

I'm not sure, but shouldn't you be using SQL server functions?

like: SELECT convert(datetime, TimeWritten) as mydate FROM dbo.tlbMyTable
I built a sample table in an access db - test;
created one text column - test;
inserted "11/1/2002" into the column;
created a sample query (in access - not via ado from outside) - SELECT CDATE(test.test) as myCDATE, format(test.test,"MM/DD/YYYY") as myFormat
FROM test;

my results (without error):

myCDATE        myFormat
11/1/2002      11/01/2002

I dont' have SQL Server set up near by to test, but my access (2k) doesn't give an error on the conversion above, but to convert a date to varchar in SQL Server (T-SQL) should be implicit;

If you're trying to execute the SQL in code or as a pass-through query, need to use SQL Server's CAST function:

SELECT CAST(TimeWritten AS datetime) mydate, *
FROM dbo.tlbMyTable

Otherwise should work from MS Access Query window.


No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
 - PAQ'd and pts removed
Please leave any comments here within the
next seven days.


Finalized as proposed


Community Support Moderator
Experts Exchange

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial