?
Solved

Insert or update datetime into SQL Server varchar field

Posted on 2008-10-15
16
Medium Priority
?
2,616 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 

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 2000 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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. …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

649 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