Solved

Insert or update datetime into SQL Server varchar field

Posted on 2008-10-15
16
2,592 Views
Last Modified: 2012-08-13
I'm developing a web site in ASP.NET with VB.NET.  I'm drawing a blank on how to properly convert the current time now() into proper datetime format to Insert or Update a varchar field in SQL Server.  The rest of the time references in the database are in the format 200810152132.

Dim AddToCatalog As String = "INSERT INTO catalog_requests (firstname, lastname, company, address, city, state, zip, catalog1, catalog2, catalog_sent, last_updated) VALUES " & _
        "('strFname','strLname','strCompany','strAddress','strCity','strState','strZIPplus','strCatalog1','strCatalog2','N','now()')"
        Dim CmdAdd As New SqlCommand(AddToCatalog, MyConn)
        MyConn.Open()
        CmdAdd.ExecuteNonQuery()
0
Comment
Question by:OVC-it-guy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 5
16 Comments
 
LVL 5

Expert Comment

by:jfmador
ID: 22727804
you can use Convert function on SQL

convert(varchar(17),GETDATE(),120)
if you don't want space, : or - in the date add this
replace(replace(replace(convert(varchar(17),GETDATE(),120),'-',''),':',''),' ','')
The varchar(17) is important if you don't want to get the second in your date, if you want them use varchar(20)
0
 

Author Comment

by:OVC-it-guy
ID: 22727877
Ok, maybe it's late and I'm tired, maybe I didn't state correctly what I'm after.  It's 10:09 PM right now on October 15, 2008.  If I insert a record into my database right now, I need the datetime to be stored in a varchar field of my SQL Server database table in the following format:
20081015220939

I just need what I insert into my SQL statement after VALUES in place of the 'now()'

Will convert(varchar(20),GETDATE(),120) do that for me?
0
 
LVL 5

Expert Comment

by:jfmador
ID: 22727947
Here we are the october 15th and it is 11:37pm

convert(varchar(20),GETDATE(),120) will give you 2008-10-15 23:38:15
this is the reason why I add 3 replace is to remove the ":", "-" and " "

then
replace(replace(replace(convert(varchar(20),GETDATE(),120),'-',''),':',''),' ','')
give me  20081015233815


0
More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

 

Author Comment

by:OVC-it-guy
ID: 22733166
Giving it a try.
0
 
LVL 5

Expert Comment

by:jfmador
ID: 22733978
Hi OVC does it work ?
0
 

Author Comment

by:OVC-it-guy
ID: 22740408
STill working through other errors on the page (unrelated to this thread).
0
 

Author Comment

by:OVC-it-guy
ID: 22743241
Didn't work.  When I run it, it sees the commas in the following
replace(replace(replace(convert(varchar(20),GETDATE(),120),'-',''),':',''),' ','')
like the separators between values, so I get an error about the number of columns not matching with the number of values.
0
 

Author Comment

by:OVC-it-guy
ID: 22743523
Is there some more like this:
Dim myTime = DateTime.Now.ToString("yyyymmddhhmmss");
but in VB.NET?
0
 

Author Comment

by:OVC-it-guy
ID: 22743869
Yep, I got closer:
Dim myTime As String = System.DateTime.Now.ToString("yyyymmddhhmmss")
gave me the following:
20083617013631
(not quite the format I wanted) along with this error:
Incorrect syntax near '20083617013631'.

Help, anyone?
0
 
LVL 5

Accepted Solution

by:
jfmador earned 500 total points
ID: 22743885
are you running the query that I mentionned on a SQL Server ?

Try this : SELECT replace(replace(replace(convert(varchar(20),GETDATE(),120),'-',''),':',''),' ','')

This function must be run on SQL Server side and not in your code

Dim AddToCatalog As String = "INSERT INTO catalog_requests (firstname, lastname, company, address, city, state, zip, catalog1, catalog2, catalog_sent, last_updated) VALUES " & _
        "('strFname','strLname','strCompany','strAddress','strCity','strState','strZIPplus','strCatalog1','strCatalog2','N',replace(replace(replace(convert(varchar(20),GETDATE(),120),'-',''),':',''),' ',''))"
        Dim CmdAdd As New SqlCommand(AddToCatalog, MyConn)
        MyConn.Open()
        CmdAdd.ExecuteNonQuery()


Or else you can use ToString("yyyyMMddHHmmss") with vb.Net
0
 
LVL 5

Expert Comment

by:jfmador
ID: 22743894
MM stand for Month since mm for Minutes
HH for 24h since hh for 12h

0
 

Author Comment

by:OVC-it-guy
ID: 22744076
Got the time thing working, but now I get the error:
There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.
Both Insert and Values have the same number of parts, so what wrong?
Dim myTime As String = System.DateTime.Now.ToString("yyyyMMddHHmmss")
        Dim strAddress As String = String.Concat(txtAddress1.Text, txtAddress2.Text)
        Dim AddToCatalog As String = "INSERT INTO catalog_requests (id, firstname, lastname, company, address, city, state, zip, best_care_catalog, first_place_catalog, catalog_sent, last_updated) VALUES" & _
        " ('" & myTime & "','" & strFname & "','" & strLname & "','" & strCompany & "','" & strAddress & "','" & strCity & "','" & strState & "','" & strZIP & "','" & str2PSDCatalog & "','" & str2FPECatalog & "','N','" & myTime & "')"
        Dim MySqlCmd As New SqlCommand(AddToCatalog, MyConn)
        MyConn.Open()
        MySqlCmd.ExecuteNonQuery()

Open in new window

0
 

Author Comment

by:OVC-it-guy
ID: 22744103
Oh, and Jfmador, you code probably would have worked, but I have the Insert/Values issue getting in the way now.  See anything wrong with it?
0
 

Author Comment

by:OVC-it-guy
ID: 22744325
Oops, my error is not longer with the code snippet of my previous post.  Same error, but it's with THIS code. And the frustrating part is that every one of my variables is a string, no decimals.  SO, please tell me, what's wrong?
Dim AddCustomer As String = "Insert into customer (id, first_name, last_name, company_name, billing_address1, billing_address2, billing_city, " & _
        "billing_state, billing_zip, billing_plusfour, email, phone1, phone2, receives_pet_newsletter, receives_equine_newsletter, receives_pet_catalog, " & _
        "receives_equine_catalog, shipping_first_name, shipping_last_name, shipping_company_name, shipping_address1, shipping_address2, shipping_city, " & _
        "shipping_state, shipping_zip, shipping_plusfour, shipping_phone, cc1, cc1_exp, name_on_card, last_updated) Values ('" & customerID & "','" & strFname & "','" & strLname & "', " & _
        "'" & strCompany & "','" & strAddress1 & "','" & strAddress2 & "','" & strCity & "','" & strZIP & "','" & strPlusFour & "','" & strEmail & "','" & strPhone1 & "','" & strPhone2 & "','" & strEmailPSD & "','" & strEmailEquine & "', " & _
        "'" & strPSDCatalog & "','" & strFPECatalog & "','" & strShipFirst & "','" & strShipLast & "','" & strShipCompany & "','" & strShipAddress1 & "','" & strShipAddress2 & "','" & strShipCity & "','" & strShipState & "', " & _
        "'" & strShipZIP & "','" & strShipPlusFour & "','" & strShipPhone & "','" & strCC & "','" & strCCExp & "','" & strNameOnCard & "','" & myTime & "')"
        Dim Cmd3 As New SqlCommand(AddCustomer, MyConn)
        Cmd3.ExecuteNonQuery()

Open in new window

0
 

Author Comment

by:OVC-it-guy
ID: 22744370
My bad again.  I declare:
Dim customerID As Integer
If all the other variables are strings, is there something I should do to the value of customerID before I insert the record into the database?
0
 

Author Comment

by:OVC-it-guy
ID: 22744592
Found it.  Nevermind.  End of thread.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

688 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