convert string to DateTime

Hi,

I have an application that allows the users to enter a date through a date picker. The date goes into the textbox as mm/dd/yyyy. I then run an INSERT. In the table I have the field set up as DateTime, but it still goes into the table as mm/dd/yyyy. Is there some way I can convert this to display the time in the database as well? I don't need it on the display, but I would like to have it in the table. I'd appreciate any help you could offer. Thank you in advance!
savache27Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Maverick_CoolCommented:
In sql server u can set date format whhich it will be stored, for example:
SET DATEFORMAT dmy

please try and let me know , u can find help from sql 2000 help files
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mdouganCommented:
Well, if you've declared your database field as a datetime, then the field is stored in a datetime format, which has nothing to do with the way that your dates appear when you select that date column.  I believe that there are ways to set the default display date format for date fields in SQL Server, however, I've always explicitly coded the format conversion in my select statements.

Try doing this

Select  CONVERT(varchar(20), mydatefield, 120) as display_date from mytable
0
mdouganCommented:
The third parameter in the Convert statement can alter the way the datetime field is displayed.  Make sure that the varchar definition is big enough to hold all the digits returned, or else you'll get some truncation.  Lookup the Cast and Convert statement in the Transact SQL help file to find all the various options for this 3rd parameter.
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

savache27Author Commented:
Well, I'm not sure what the problem is. Any other time that I have used a datetime field it shows the time in the database as well, but this does not. Of course, I think I typically use the .Now() to insert and with this application I allow the user to enter the date. I just need the time b/c I am displaying when a user has contacted someone and if two users contact someone on the same day it pulls the name in alpha order, rather than the last date. This is the sql I'm using:

              sql2 = "SELECT top 1 * FROM myTable where RECID = " & dtr("id") & " ORDER By STAMP DESC"

I just figured that if I had the time in the datetime field it would help with that.
0
savache27Author Commented:
I tried this for the insert:

 Dim vDate as String
 Dim vDateTime As DateTime      
 vDate = Request("frmContactDate")
 vDateTime = Convert.ToDateTime(vDate)


sql = "INSERT INTO myTable  ([RecID],[ContactedBy],[Comment],[Stamp]) VALUES (" & vIdArray(i) & ",'" & Session("gUserName") & "','" & vNote & "','" & vDateTime & "');"

but that didn't work. I'm not sure what to do.
0
savache27Author Commented:
I appreciate the help! That worked.
0
mdouganCommented:
Oh, well, your problem is that you're getting just a Date from a date picker on the user interface.  When you use the ToDateTime, sure, it will convert the date into a DateTime, however the time values are going to be 00:00:00 because they didn't come in from the date control.  I'll bet that if you display the value in vDateTime, it will look like     1/30/2008 00:00:00  the time values will always be zero.

If you are trying to sort by some sort of timestamp based on when things were added to your table, then you should separate out the user choosen date field from the timestamp field.  You should have the Insert statement use getdate() to insert the SQL Server datetime into the timestamp field.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.