.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan
Asad Ali
Fauzul Azmi
Post New Web Links

How do I convert a Sql Datetime value into a string including the miliseconds?

Posted By:      Posted Date: September 23, 2010    Points: 0   Category :ASP.Net
 

 Hi all,

Currently I am able to bring the datetime from my SQL table but I also need the milisecods. Here is the line of code:

DateTime latestTimeStamp = DateTime.UtcNow;
string select = "select max(timeStamp) from Table1";

SqlConnection myConnection = new SqlConnection(WebConfigurationManager.ConnectionStrings["UctConnectionString"].ConnectionString);
SqlCommand mySqlCommand = new SqlCommand(select, myConnection);
mySqlCommand.Connection.Open();

latestTimeStamp = (DateTime)mySqlCommand.ExecuteScalar();

So latestTimeStamp = {9/22/2010 6:24:09 PM} but I needed it to be latestTimeStamp = {9/22/2010 6:24:09:080 PM}
since the SQL field value is 2010-09-22 18:24:09.080 and I want to do an accurate datetime comparison.

Thank you




View Complete Post


More Related Resource Links

Convert SSIS DateTime to a String

  

Being a newbie to SSIS I'm not sure of the most efficient method of converting a DateTime object to a String.

 

I'm from a C# background where this would be easy using DateTime.ToString("YYYYMMdd"). I want to use the date in a file name so don't require most of the parts.

 

I'm sure I could do this using a script task to produce a file name for each row of data in my table and add that filename to the dataset but it seem like overkill to do something that should be simple. Also as I'm supposed to be getting to grips with SSIS I shouldn't keep running back to what I know.

 

My current approach is to derive a column and build up an expression to convert the date into a string. The only problem being that it doesn't work.

The expression I'm working with is:

(DT_WSTR, 50)([OrgName] ) + "_" + (DT_WSTR, 50)( [PayrollName] ) + (DT_WSTR, 4)(YEAR( [ProcessedDate] )) + (DT_WSTR, 2)(MONTH( [ProcessedDate] )) + (DT_WSTR, 2)(DAY( [ProcessedDate] )) ".txt"

 

Can anyone see where I'm going wrong?

 

All comments greatly received.

Convert String "" To datetime

  

I have a problem about convert a string to datetime.

in my database i have table with canceldate column.

that column have a empty row (like '' but not null ) and string (like '01-12-2004').

when i run query this message was show "The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value."

if i use this query "convert(datetime,canceldate,105)" the empty row was convert to 1900-01-01 00:00:00.000.

i want convert that coloumn without change value of empty row.

any idea?

 

Thanks

Surbakti


Convert String DateTime

  

Hi,

A texbox which sets a date (MM/dd/yyyy) format and I want to add current time(HHmmss) to that date and assign this to a DateTime variable. At the end output format should be in yyyyMMddHHmmss and this should be in DateTime fied.

Is it possible? I tried as below but never succeeded. Could you please help me to resolve this!!

 

DateTime date = Convert.ToDateTime(TextBox1.Text);

 

TimeSpan time = new TimeSpan(23, 50, 0);

date = date.Add(time);

 

dropdown list fill with Linq and convert string to datetime and ordering and stored procedure call.

  

I wanted to share this more than anything,  it took me most of the morning searching and finding various post on various sites but I got it figured out and working.  It might be better coded, but it is a start for some one trying to understand how linq and ASP.Net things work.

What I have is a database table with a datetime in string format and a stored procedure which returns all the data.
What I want to do is load a dropdown list with MonthName and Year for a selection choice on generating monthly report.

The stored procedure is in a Linq to SQL class dbml and the connection string is dynamic, i.e., made througha call to another class.

here is the code.  Enjoy understanding how it works.

// Miscellaneous Details are filled in for helping you get the big picture.
// Some of the using statements are for other things in the code behind, 
// but I left it in so don't get confused,
using System;
using System.Collections.Generic;
using System.Linq;
using System.Collections.ObjectModel;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Security;
using MYProject.LinqtoSQLStuff;

namespace MYProject.ChooseReportPage
{
	 public partial class TheReportPage : System.Web.UI.Page  
	 {
            
	     protected void Page_Load(object sender, EventArgs e)
            {

   

Failed to convert parameter value from a String to a Guid.

  

I am trying to get the UserRole from the logged in user and send it to the database table my code is:

SqlParameter FamilyFriendsOther = new SqlParameter("@Role", SqlDbType.UniqueIdentifier);
        string [] roles = System.Web.Security.Roles.GetRolesForUser();
        if (roles.Length > 0)
            FamilyFriendsOther.Value = roles[0];
        cmd.Parameters.Add(FamilyFriendsOther);



How Convert Datetime (YYYY/MM/dd HH:mm:ss) To yyyy-MM-dd

  

Hello,

How do you write a date value to an XMLElement in .NET 3.5 XMLDocument object. 

 

 I am using the following:

 

DateTime orderDate = new DateTime();
orderDate = DateTime.Now; // 2010-08-24 16:45:46

string st = XmlConvert.ToString(orderDate, "yyyy-MM-dd");     // st="2010-08-24"
DateTime dt2 = XmlConvert.ToDateTime (st, "yyyy-MM-dd");   // dt2 = "2010-08-24 00:00:00"

I would like to remove the time section "00:00:00". I only need to write a date in the XML file with the following format :

<OrderDate>2010-08-24</OrderDate>

Order date is defined like this in the xsd file : <xs:element name="OrderDate" type="xs:date"/>

 

Thanks for your support.


convert english string to hindi string

  
Code to convert english to hindi string

Data Conversion - string to datetime help!!!

  
Hi all, I need help on concatenate two fields into one then display it as datetime format as such yyyy-mm-dd hh:mm:ss AM/PM. The db is on SQL 2008 SP1. The date and time columns are on the same table and store as varchar. Date has varchar(10) and time has varchar(16). Date column shows result from query is 20100827 while the time column is either 070101.000000 or 070101. The question is how go about concatenate the two together and display in the format above in a report? How should I write my select query for this report? I am using SQL 2008 R2 BI Development Studio to design my report. Thank you. VCHS

Convert XML DateTime to SQL DateTime

  
I have tried to convert XML datetime value to SQL datetime by executing the below statement, though I feel there might be some other way of converting the value in SQL server 2000. declare @TestXMLTime as varchar(50) set @TestXMLTime ='2010-01-01T00:00:07+05:30' select cast(left(@TestXMLTime,10)+' '+right(left(@TestXMLTime,19),8) as datetime)    

Convertion failed when converting datetime to String

  
DECLARE @usr datetime SET @usr = user+'::Date' IF NOT EXISTS (SELECT 'X' FROM table WHERE Name = 'File' AND CONVERT(varchar,DateAdd, 101) = CONVERT(varchar, getdate(), 101)) BEGIN INSERT INTO table (Code, Name, Date) VALUES ('DD', 'File',@usr) END the date will be created by another process and its value will be loaded in the variable DATE. I'm calling that variable here. I;m getting the error Convertion failed when converting datetime to String. how to resolve this..??

Convertion failed when converting datetime to String

  
DECLARE @usr datetime SET @usr = user+'::Date' IF NOT EXISTS (SELECT 'X' FROM table WHERE Name = 'File' AND CONVERT(varchar,DateAdd, 101) = CONVERT(varchar, getdate(), 101)) BEGIN INSERT INTO table (Code, Name, Date) VALUES ('DD', 'File',@usr) END I;m getting the error Convertion failed when converting datetime to String. how to resolve this..??

Failed To Convert Parameter Value From A String To A Int32

  
 This is my stored procedure: PROCEDURE[dbo].[uspEXPENDITURE_DETAILS] ( @ExpDetailsID int=0 output, @ExpDet_ExpID int, @ExpDet_Municipality_ID nvarchar(50), @ExpDetGeneralFund money, @ExpDetSpecialRevenue money, @ExpDetCapitalProjects money, @ExpDetTotal money, @Exp_Reporting_Year varchar(10), @ExpComments varchar(max), @Contact_ID int ) AS BEGIN  -- SET NOCOUNT ON added to prevent extra result sets from  -- interfering with SELECT statements.  SET NOCOUNT ON; deletefrom [dbo].[TBL_EXPENDITURE_DETAILS] where ExpDet_ExpID=@ExpDet_ExpID and ExpDet_Municipality_ID=@ExpDet_Municipality_ID and Exp_Reporting_Year=@Exp_Reporting_Year If@ExpDetailsID >= 0 Begin INSERT     INTO [dbo].[TBL_EXPENDITURE_DETAILS] ([ExpDet_ExpID] ,[ExpDet_Municipality_ID] ,[ExpDetGeneralFund] ,[ExpDetSpecialRevenue] ,[ExpDetCapitalProjects] ,[ExpDetTotal] ,[Exp_Reporting_Year] ,[ExpComments] ,[ExpDetCreatedBy] ,[ExpDetCreateDate]) VALUES  (@ExpDet_ExpID , @ExpDet_Municipality_ID , @ExpDetGeneralFund, @ExpDetSpecialRevenue , @ExpDetCapitalProjects, @ExpDetTotal, @Exp_Reporting_Year, @ExpComments, @Contact_ID, GetDate () )    SET @ExpDetailsID = SCOPE_IDENTITY()  return @ExpDetailsID end   Public Sub CreateExpenseDetails() Dim item As GridViewRow Dim txtExpGenFund, txtExpD

Convert float to String

  
HI, I am creating a derived column in my workflow. This derived column would have a concatenated value or Currency + [Account No]. Derived column is of type DT_STR. Currency is string (which is fine). I am having trouble converting [Account No] from float to string I tried Currency + (DT_WSTR)[Account No] and Currency + (DT_STR)[Account No] I am a newbie here... so, excuse my stupid question

Varchar string to int - how to convert empty cells to nulls

  
Hi, I have a large table from which I want to convert certain columns from varchar to int while loading the table to another table. However, I run into problems when the input column contains empty cells. The SSIS data conversion data flow item can't handle these so I tried the script component. With the script component I can convert these empty cells to zeros but after that I'd need to use SQL update to update the zeros to nulls which in the case of a large table with over 3 million rows takes unnecessarily long. So, is there another way?

failed to convert parameter value from a string to a int32

  
I am not sure where my app is failing but most probably this is where it is failing.. I think lblExpID.Text is the culprit. Here's the relvant part of my code: Dim lblExpID As Label For Each item In LSGrid.Rows lblExpID = item.FindControl("lblID") If txtExpGenFund.Text <> "" Then sqlArray(i) = ExpDetailsID & "," & (lblExpID.Text) & "," & MunID & "," & _ Val(txtExpGenFund.Text) & "," & Val(txtExpDetSpecialRevenue.Text) & "," & _ Val(txtExpDetCapitalProjects.Text) & "," & expTotal & "," & reportingYear & "," & "Test Comments" & "," & UserID End If Can u help me identify why i am getting this error looking at the sp below: PROCEDURE [dbo].[uspEXPENDITURE_DETAILS] ( @ExpDetailsID int=0 output, @ExpDet_ExpID int, @ExpDet_Municipality_ID nvarchar(50), @ExpDetGeneralFund money, @ExpDetSpecialRevenue money, @ExpDetCapitalProjects money, @ExpDetTotal money, @Exp_Reporting_Year varchar(10), @ExpComments varchar(max), @Contact_ID int ) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; delete from [dbo].[TBL_EXPENDITURE_DETAILS] where ExpDet_ExpID=@ExpDet_ExpID and ExpDet_Municipality_ID=@ExpDet_Municipality_ID and Ex

How to pass a String and convert to xml

  
A Simple Query Pass to connection String 1 My Query is SELECT a.HD, a.HEAD, a.SH, a.SUBHEAD, a.TRK, a.TRACK, a.TGT, a.TARGET, a.PDC, a.DT_RELEASE, a.STATUS, a.COFROM V_HIERARCHY1 a   2,My Data is HD    HEAD    SH    SUBHEAD    TRK    TRACK    TGT    TARGET    PDC    DT_RELEASE    STATUS    COHR    HR    Rec    Records    Leave    Leave Records    HRL.0002    Quarterly Review    01.07.2010    04.07.2010    Planned    EdlinkHR    HR1    DB    Database    SAL    Salary    DDS.0001    Calculation of Monthly salary    08.08.2010    09.08.2010    WIP    EdlinkHR    HR    Rec    Records    Ind    Induction Records    HRI.0001    Completion of dossiers.    08.07.2010    10.07.2010    WIP    EdlinkHR    HR  

Convert a string (from xml) into date time

  
Is there a simple command to take this string: 2010-09-09T17:00:00.000Z and turn it into a date time? It is a date time value that is passed to us in an xml document, it has a type of 'unitless', when I read it I need to set a dateTime variable to this date and tim, I can parse it, but perhaps there is a simple single command that is already written for just such a thing. Thanks. 
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend