By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,454 Members | 3,191 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,454 IT Pros & Developers. It's quick & easy.

TSQL INSERT Date Format

benchpolo
100+
P: 142
Expand|Select|Wrap|Line Numbers
  1. Public Sub cmdOMWUpdate_Click()
  2. On Error GoTo cmdOMWUpdate_Click_Err
  3.  
  4.     Dim strSql As String
  5.     Dim curDate As String
  6.  
  7.     DoCmd.SetWarnings False
  8.  
  9.     curDate = Now()
  10.  
  11.     strSql = "INSERT INTO dbo_HEDIS_Quarterly_Letter (type, memberid, language, datemailed, lettersend) " & _
  12.             "SELECT HedisType, MemberID, Language, " & Format(Now(), "mm/dd/yyyy") & ", 'Y' from members"
  13.  
  14.  
  15.     DoCmd.RunSQL strSql
  16.     DoCmd.SetWarnings True
  17.  
  18. cmdOMWUpdate_Click_Exit:
  19.     Exit Sub
  20.  
  21. cmdOMWUpdate_Click_Err:
  22.    MsgBox "An unexpected error has occurred." & _
  23.    vbCrLf & "Please note of the following details:" & _
  24.    vbCrLf & "Error Number: " & Err.Number & _
  25.    vbCrLf & "Description: " & Err.Description _
  26.    , vbCritical, "Error"
  27.     Resume cmdOMWUpdate_Click_Exit
  28.  
  29. End Sub
  30.  
I am trying to insert the current date into a SQL table, and after the code ran the value date value is coming out as 1899-12-30 00:00:00.000 I tried to do a format(Now(),"mm/dd/yyyy") yet it is inserting an incorrect date and format. Please advice.

Thanks.
Jun 12 '12 #1
Share this Question
Share on Google+
2 Replies


benchpolo
100+
P: 142
Expand|Select|Wrap|Line Numbers
  1. curDate = Format(Now(), "mm/dd/yyyy", vbUseSystemDayOfWeek, vbUseSystem)
  2.  
  3.     strSql = "INSERT INTO dbo_HEDIS_Quarterly_Letter (type, memberid, language, datemailed, lettersend) " & _
  4.             "SELECT HedisType, MemberID, Language, " & Format(Now(), "mm/dd/yyyy") & ", 'Y' from members"
  5.  
here is the section of the code
Jun 12 '12 #2

Expert 100+
P: 634
Hi

Having just gone through the loop of querying an SQL Server DB, I think you need to delimit the date with aphostropies (not a Hash as in Access) like this
Expand|Select|Wrap|Line Numbers
  1.  strSql = "INSERT INTO dbo_HEDIS_Quarterly_Letter (type, memberid, language, datemailed, lettersend) " & _ 
  2.             "SELECT HedisType, MemberID, Language, '" & Format(Now(), "mm/dd/yyyy") & "', 'Y' from members"

HTH

MTB
Jun 14 '12 #3

Post your reply

Sign in to post your reply or Sign up for a free account.