How to convert Date in SQL-query?

[Garett]

Hi guys, need some help with SQL-query to *.mdb file. Trying to read date/time field but I need format DD.MM.YYYY but receiving YYYY-MM-DD HH:MM:SS This is my SQL-query to database from MFC (Visual C++):

CRecordset recset2( &database );


SqlString2 = "SELECT Main.[S/N], Employers.Employer, History.Date FROM Main INNER JOIN (Employers INNER JOIN History ON Employers.employer_code = History.employer_code) ON Main.device_code = History.device_code";
SqlString2+=" WHERE [S/N]=";
SqlString2+="'";
SqlString2+=sSerial;
SqlString2+="' ORDER BY History.Date";




recset2.Open(CRecordset::forwardOnly,SqlString2,CR ecordset::readOnly);
while( !recset2.IsEOF() )

{
recset2.GetFieldValue("Date",sDate);
recset2.GetFieldValue("Employer",sEmpl);
recset2.MoveNext();

}


recset2.Close();

So I need to read all the values of "Date" field in the table "History" and convert it to DD.MM.YYYY format - how to? Thank you for support.

[ranman256]

import the date by chopping it up as a string before placing it into a Date Field:

cvtStrDate()

once its converted into a Date Type field, then you can display the format as :dd/mm/yyyy

public function cvtStrDate( pvDateFld) 
dim vDate

if isnull(pvDateFld) then exit function
y = left(pvDateFld,4)
m = mid(pvDateFld,6,2)
d = mid(pvDateFld,9,2)
h = mid(pvDateFld,12,2)
n = mid(pvDateFld,15,2)
s = right(pvDateFld,2)
vDate= m & "/" & d & "/" & y & " " & h & ":" & n & ":" & s
cvtStrDate = cvDate(vDate)
end  function

[ssanfu]

BE aware that object names in Access should only be letters and/or numbers. NO punctuation or special characters.

"[S/N]" is a poor field name be of the slash. "Date" is a reserved word AND a built in function.
Plus "Date" is not very descriptive - Date of What? Hire date, Term date, Promotion date?

SqlString2 = "SELECT Main.[S/N], Employers.Employer, History.Date  FROM Main INNER JOIN (Employers INNER JOIN History ON  Employers.employer_code = History.employer_code) ON Main.device_code =  History.device_code";
SqlString2+=" WHERE [S/N]=";
SqlString2+="'";
SqlString2+=sSerial;
SqlString2+="' ORDER BY History.Date";

[Minty]

Access would normally recognise that as a valid date format - so what is the actual issue?

You could add a conversion field:

SqlString2 = "SELECT Main.[S/N], Employers.Employer, History.[Date] , CDate(History.[Date]) as MyDate FROM Main INNER JOIN (Employers INNER JOIN History ON  Employers.employer_code = History.employer_code) ON Main.device_code =  History.device_code";SqlString2+=" WHERE [S/N]=";
SqlString2+="'";
SqlString2+=sSerial;
SqlString2+="' ORDER BY History.Date";