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

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!
0
savache27
Asked:
savache27
  • 3
  • 3
1 Solution
 
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
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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