Solved

Insert or update datetime into SQL Server varchar field

Posted on 2008-10-15
16
2,569 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
  • 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
 

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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

911 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

21 Experts available now in Live!

Get 1:1 Help Now