Reformat dates with SQL?

[Dan01]

Hi, dates are stored yyyymmdd in a database. Is it possible to select dates and reformat them on the fly so that the recordset contains mm/dd/yy? Thanks, Dan.

 

[rac2]

Which brand of database?
Are the dates stored in DATETIME, VARCHAR, or INT fields?

 

[Dan01]

Hi rac2, the dates are stored in image database as x8 character fields. Thanks, Dan.

 

[rac2]

Hello Dan,

Probably this won't help because you probably are not using MS SQL Server, but if you were -

SELECT CONVERT(CHAR(8), CONVERT(DATETIME, myFieldOfDates), 1) FROM myTableOfImages

How that works is -
Assume we have a table named myTableOfImages,
and a column in that table named myFieldOfDates which is a CHAR(8) datatype. (CHAR(8) being the SQL Server notation that might correspond to x8 character)

Then CONVERT(DATETIME, myFieldOfDates) will convert the character string yyyymmdd into a DATETIME value. Call this X for convenience.

Then CONVERT(CHAR(8), X, 1) converts that value into a string with a format like mm/dd/yy. The number 1 indicates the format.

So, if you are working with a different brand of database such as Access or Oracle then the conversion function will be different but the same approach might work.

Briefly, string i have -> date/time -> string i want.

 

[Dan01]

Thanks a bunch rac2! Dan.