Excel VBA SQL Query - Format Date Output

[dave981]

I'm using a SQL query in VBA to select and join data spread across two worksheets and then outputting the results to a third worksheet. One of the fields is an Excel Date.

On the source worksheet the value is: 9/25/2008 21:05
On the output worksheet the result is: 38996.402222

I would like the output to also be formatted: MM/DD/YYYY HH:MM

I read a reference to "DateAdd" somewhere... So i tried the following, but I do not receive any results.

stSQL = "SELECT [Worksheet1$].[Last Updated Date], DateAdd([Worksheet1$].[Last Updated Date], -2, 39083)  FROM [Worksheet1$]"

Thoughts/ guidance?

I would prefer to perform the formatting as part of the SQL query, rather than trying to capture the result during output. For now i just write out the entire table of results to the output sheet.

Thanks,
Dave

 

[xenou]

Hi, formatting is not part of the SQL language per se, and its best to not try to use it for that.

Is this return value a date/time or the difference between two dates/times? If the former, format the output sheet for dates, if the latter format the output sheet for an integer or decimal value. You can "finish" your vba code with formatting statements that work on the excel sheet where you have the output data. Your "number" looks like a date. Excel is telling me its Oct 6, 2006 at 9:39 AM...

If you have further questions paste a little more of your code. Actually, I'd be interested in seeing that anyway.

--AB

 

[NateO]

You could try CDate() in your SQL, but it might truncate your time, e.g.,

http://www.mrexcel.com/forum/showthread.php?p=1587009

Otherwise, cell formatting might be the way to go.

 

[dave981]

NateO - The CDate() function worked for me. It would be nice to be able to pull through the time too - but date meets 90% of my needs.

Thanks!
Dave

 

[NateO]

You are welcome.

 

[xenou]

Good call Nate. I guess I expected DateAdd to return a date already.