Solved

Insert or update datetime into SQL Server varchar field

Posted on 2008-10-15
16
2,585 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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

733 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