• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2627
  • Last Modified:

Insert or update datetime into SQL Server varchar field

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
OVC-it-guy
Asked:
OVC-it-guy
  • 11
  • 5
1 Solution
 
jfmadorCommented:
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
 
OVC-it-guyAuthor Commented:
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
 
jfmadorCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
OVC-it-guyAuthor Commented:
Giving it a try.
0
 
jfmadorCommented:
Hi OVC does it work ?
0
 
OVC-it-guyAuthor Commented:
STill working through other errors on the page (unrelated to this thread).
0
 
OVC-it-guyAuthor Commented:
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
 
OVC-it-guyAuthor Commented:
Is there some more like this:
Dim myTime = DateTime.Now.ToString("yyyymmddhhmmss");
but in VB.NET?
0
 
OVC-it-guyAuthor Commented:
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
 
jfmadorCommented:
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
 
jfmadorCommented:
MM stand for Month since mm for Minutes
HH for 24h since hh for 12h

0
 
OVC-it-guyAuthor Commented:
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
 
OVC-it-guyAuthor Commented:
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
 
OVC-it-guyAuthor Commented:
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
 
OVC-it-guyAuthor Commented:
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
 
OVC-it-guyAuthor Commented:
Found it.  Nevermind.  End of thread.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 11
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now