Convert Text to Date Format (TO_DATE)

Ranjit9
Ranjit9 used Ask the Experts™
on
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.
Ranjit
Comment
Watch Question

Do more with

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

Commented:
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.

Commented:

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.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
Commented:
Finalized as proposed

modulo

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