date syntax

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to concantenate a date with a time in an insert query:

Insert into table1 t (ApptID, ApptTime, ApptTypeID)
(SELECT tApptID, dtDate + tApptTime, tApptType FROM table2)

What is the syntax for this if dtDate is a variable of type date?

Thanks!!

sam
 
If I understand correctly, dtDate is a memory variable and you want to
create/run an append query in code. If so, you would use something like:

Dim strSQL as String
strSQL = "Insert into table1 t (ApptID, ApptTime, ApptTypeID) " & _
"(SELECT tApptID, #" & Format(dtDate,"mm/dd/yyyy") & _
"# + tApptTime, tApptType FROM table2)"
DoCmd.RunSQL strSQL
 
"SELECT tApptID, #" & Format(dtDate,"mm/dd/yyyy") & _
"# + tApptTime, tApptType FROM table2"
Click to expand...


If dtDate is a DateTime which is _guaranteed_ to have
TimeValue(dtDate)=0

and tApptTime is a DateTime which is _guaranteed_ to have
TDateValue(tApptTime)=0

then you can concatenate them within the Format:


"SELECT tApptID, " & _
Format(dtDate + tApptTime, "\#yyyy\-mm\-dd hh\:nn\:ss\#") & ", " & _
tApptType " & _
"FROM table2"




All the best

Tim F
 
Great! This is a big help. Strangely enough, though, the date part is two
days later than dtDate. If dtDate is 9/20/06, then my time is reading 9/22/06
10:00 AM
??
 
I figured it out: we had set the default date (when none supplied) to be
1/1/1900 rather than the 12/30/1899 supplied by MS Access in the time field.
This was in order to be compatible with SQL server smalldatetime data type
(which doesn't accept 12/30/1899).
 
Back
Top