Formatting Dates from SQL to Access

[smorgan77]

I have a linked table from SQL 2005 and the date format is YYYY-MM-DD HH:MM:SS. I need to populate an Oracle table with the date, but the format is different, it's YYYYMMDD. I can't find the correct way to convert this in Access.

 

[formerTexan]

Hi,

The following will return 2009/08/27 from 2009/08/27 09:00:00 AM

Dim dt as date
dt = Now()
dt = Format(dt, "yyyy, mm, dd")

If you are looking for 20090827 then you will probably have to parse out the date components and reconstitute as a string which can then be passed to Oracle to be reconverted back to a date value.

Cheers, Bill

 

[SkipVought]

Hi,

The Format function returns a STRING.

Dim sDt as string
sDt = Format(Now(), "yyyymmdd")

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lameid]

I wouldn't think it would matter the format of the date unless there was a United states vs someplace else issue and you were concerned about month day vs day month.

If it is text to start, you may want to start with the CDate function.

Regardless, the format function is part of the solution if you are using text and format matters.