vbscript sql syntax problem with datetime

Posted on 2012-09-06
Last Modified: 2012-09-10
Hi All,

I am extracting data from one database table and importing into another database table.
One of the fields I need to populate is a datetime field.
I just cant seem to get it to work in my vbscript.

Extracts below so you can see what I have been doing and tried.

What do I need to put in my sql string to populate this field        

      strMemberTypeId = "{DA053225-8C79-452F-A49B-31BE596C6C4B}"
      strMembershipProductId = "{361159A2-590C-4B33-9181-281BE0478D62}"
      'strRenewdate = FormatDateTime("2040-01-21 00:00:00")
      'Dim strDate As Date  
      'strRenewDate = "2040-01-21"

      SQLString = DECLARE @datetime datetime = '2040-01-21 00:00:00';
      'SQLString = DECLARE @datetime datetime = @smalldatetime;
      SQLString = SQLString & "[MemberTypeId],[MembershipProductId],[Renewal_Date]


Question by:sid20vt
    LVL 16

    Assisted Solution

    Well, you need to surround your date in single quotes for SQL server like:

    INSERT INTO T_MEMBERS('2040-01-21') (assuming your table only has one field and it is the datetime field)

    better is

    INSERT INTO T_MEMBERS (MyColumnName) VALUES ('2040-01-21')
    LVL 26

    Assisted Solution

    by:Alan Warren
    Hi Sid,
    You could give the field [Renewal_Date] a default value/binding of GetDate() in the table designer, then you could exclude the parameter from the Insert SQL. Having said that, there may be instances when you don't want to use todays date for the default value, in which case you can pass in any date using your SQL.
        ' Guids can be passed in as strings, just lose the braces.
        Dim strMemberTypeId As String = "DA053225-8C79-452F-A49B-31BE596C6C4B"
        Dim strMembershipProductId As String = "361159A2-590C-4B33-9181-281BE0478D62"
        Dim SQLString As String = ""
        Dim Renewal_Date As Date
        ' Renewal_Date = Date.Now
        ' or 
        ' Renewal_Date = Convert.ToDateTime(Me.Renewal_Datetextbox.Text)
        ' or
        Date.TryParse(Me.Renewal_Datetextbox.Text, Renewal_Date)
        SQLString += "INSERT INTO T_MEMBERS ([MemberTypeId],[MembershipProductId],[Renewal_Date]) "
        SQLString += "Values (" & strMemberTypeId & "," & strMembershipProductId & "," & Renewal_Date & ")"
        ' copy from the debug window and validate the syntax in your db query window.

    Open in new window

    LVL 40

    Accepted Solution

    set c = createobject("adodb.connection") "Provider=SQLOLEDB;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=<datebase-name>;Data Source=<your-server-name>"
          strMemberTypeId = "{DA053225-8C79-452F-A49B-31BE596C6C4B}"
          strMembershipProductId = "{361159A2-590C-4B33-9181-281BE0478D62}"
          strRenewDate = "2040-01-21"
    set r = createobject("adodb.recordset") "select [MemberTypeId],[MembershipProductId],[Renewal_Date] from T_MEMBERS where 1=0",c,3,3
    r("Renewal_Date") = strRenewDate

    Open in new window


    Author Closing Comment

    After all that, the solution was simple.

    The datetime column I was trying to change was a computed column so couldnt be updated.
    Other columns elsewhere needed the datetime values.
    Wasnt until I started looking at the sql table designs and editing in query that I stumbled across it.

    Thank you all for your help.  I am splitting the points.
    Sometimes all you need is someone you can bounce ideas off.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now