Convert DateTime to Text in SELECT

[dbwizzard]

IN Access 2000 I want to compose an SQL statement to create a new table from an existing one. I want to select a datetime field from the existing table and insert it as a text string in the new table. Is there some kind of SQL conversion function I can use in the SELECT statement to achieve this?

Something like:
SELECT C2STR(datefld, "mm/dd/yy") AS txtdate INTO tblNew FROM tblOld;

 

[raskew]

Hi, welcome to the forum -

Not sure why you'd want to represent a datefield as a textfield since, in the process, you lose the capacity to use date-related functions as well as the ability to correctly sort by date.

Having said that, lookup the cstr() function. Example:

x = date()
? x
5/12/2010 
...to return x as text:
? cstr(x)
5/12/2010

HTH - Bob

 

[pbaldy]

You can use the Format() function. It will result in a string value.

 

[dbwizzard]

Thanks for the suggestions, however your solutions involve using VBA code which I'm attempting to avoid. I was hoping to achieve the data conversion using SQL code only. I think I could do this in Sybase/Oracle but I'm beginning to think the Jet engine can't do it.

 

[pbaldy]

The Format() function can be used in SQL.

 

[boblarson]

My question is why are you doing this in the first place? Sounds a bit strange to me.