473,320 Members | 2,193 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

TSQL INSERT Date Format

benchpolo
142 100+
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
2 2272
benchpolo
142 100+
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
MikeTheBike
639 Expert 512MB
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

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

Similar topics

8
by: Ray in HK | last post by:
Will it be possible to specify the date format of type DATE during data loading ?
2
by: Paul | last post by:
I am trying to insert a date into a mysql table but everytime I attempt it the date field is always empty. The field is of Date type and the date in code is in European format dd/mm/yyyy. ...
2
by: Guoqi Zheng | last post by:
Dear sir, I am writting an SMTP component, I used below to insert the header date field, strMsg += "Date: " + Now.ToString("d MMMM yyyy hh:mm:ss zzzz") + vbCrLf However, my ISP always edit...
1
by: Rotsj | last post by:
Hi, i'm using visual foxpro 9 with a mysql 5 database, for direct access to my database i use navicat. In visual foxpro i've set my date format to dd-mm-yyyy, also i did this in navicat. However...
7
by: Richiep | last post by:
I am trying to get a UK format date of dd/mm/yyyy. Why does the following subroutine not return a valid date in a web form? The date returned is #12:00:00 AM# but the date I entered into the...
2
by: syntego | last post by:
We commonly use triggers to log changes to our main tables to historical log tables. In the trigger, we create a concatenated string of the old values by casting them as follows: ...
1
by: jimmy | last post by:
I'm trying to insert a date into a MySQL date column. The string i am trying to insert takes the following format: 2007-02-23 which corresponds to the date format that MySQL uses which is...
9
chandru8
by: chandru8 | last post by:
hi to all iam using vb6.0 can any one correct me the query which below mentioned is correct strSql = "Insert into table1 values (#" & Date & "# ,'2')" Set rs = objCon.Execute(strSql) or
3
by: buterfly0707 | last post by:
hi ... assume computer system date format is mm/dd/yyyy but the format which SQL is getting dd/mm/yyyy. and from the c# code by sql query i have to convert the date to sql datetime format to...
1
by: Cainnech | last post by:
Hello, I have a problem when I try to insert dates in my database. I want to insert a date range in my table in the dd/mm/yyyy format. When I insert for instance 01/05/2011 (1st of May) it...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.