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


Top 5 Contributors of the Month
MarieAdela
Imran Ghani
Post New Web Links

Converting Date Time to Minutes

Posted By:      Posted Date: October 21, 2010    Points: 0   Category :Sql Server
 

Hi,

I have a database column in datetime datatype . From the datetime column I applied the formula :

substring(convert(varchar(10),[Received time],108),1,5)

which gives me values like 06:00 (HH:MM)

I need to get the minutes from the HH:MM like 06:00 - 360 minutes ( numeric) as I will later be required to do an average of the minutes.

I need to convert HH:MM to represent only minutes in Numeric. Please let me know on this.

Thank You

 


EVA05


View Complete Post


More Related Resource Links

Conversion failed when converting date and/or time from character string. SQL 2008

  
The below simple query giving me the "Conversion failed when converting date and/or time from character string." error. Because I've  datatype of "Created" & "Finished" column in below table VARCHAR(100) select * from dbo.mytable where CONVERT(datetime,Created) >= convert(datetime,'9/1/2010') and CONVERT(datetime,Finished) <= convert(datetime,'9/9/2010') In my table i've date stored in below varchar(100) formats. examples: '2010-09-01 11:14:54.000' --Created '2010-09-03 13:43:37.000' --Finished I tried to convert the date in following way but NO luck??? Could you please advise me??? convert(datetime,convert(varchar,convert(datetime,Created),101)) >= convert(datetime,'2010-09-01') and convert(datetime,convert(varchar,convert(datetime,Finished),101)) <= convert(datetime,'2010-09-09') Much appreciated your help!! thanks, pbrathod pbrathod

Conversion failed when converting date and/or time from character string. SQL 2008

  
The below simple query giving me the "Conversion failed when converting date and/or time from character string." error. Because I've  datatype of "Created" & "Finished" column in below table VARCHAR(100) select * from dbo.mytable where CONVERT(datetime,Created) >= convert(datetime,'9/1/2010') and CONVERT(datetime,Finished) <= convert(datetime,'9/9/2010') In my table i've date stored in below varchar(100) formats. examples: '2010-09-01 11:14:54.000' --Created '2010-09-03 13:43:37.000' --Finished I tried to convert the date in following way but NO luck??? Could you please advise me??? convert(datetime,convert(varchar,convert(datetime,Created),101)) >= convert(datetime,'2010-09-01') and convert(datetime,convert(varchar,convert(datetime,Finished),101)) <= convert(datetime,'2010-09-09') Much appreciated your help!! thanks, pbrathod pbrathod

SQL error: Conversion failed when converting date and/or time from character string.

  
I hope someone can advise on the issue I'm having.

This SQL worked beautiful until I added in @startdt and @enddt and this section >> AND [Selldate] BETWEEN ' + @startdt + ' AND ' + @enddt + '

By adding in this condition, I now get the following error:

Msg 241, Level 16, State 1, Line 8
Conversion failed when converting date and/or time from character string.

I have tried changed variables to type = date and various iterations of date formatting, casting with no success. The date in the table is clean as a whistle.

(The CONVERT function is being used in setup for a PIVOT SQL which needs  VARCHAR for column headers)

Thanks in advance for your help, 
JR

declare @itemID varchar(20)
declare @startdt varchar(12)
declare @enddt varchar(12)
set @itemID = ''
set

Date and Time Functions in SQLSERVER

  
Date and time functions allow you to manipulate columns and variables with DATETIME and SMALLDATETIME data types.

1 DATEPART Function
2 DATENAME Function
3 DAY, MONTH, and YEAR Functions
4 GETDATE and GETUTCDATE Functions
5 DATEADD Functions
6 DATEDIFF Function
7 More SQL Server Functions

Data Types - Date and Time in SqlServer

  
Date and time values can be stored with either the DATETIME or SMALLDATETIME data type. The difference between the two is that SMALLDATETIME supports a smaller range of dates and does not give the same level of precision when accounting for time. The DATETIME data type can hold values from January 1st of 1753 to December 31st of 9999. The time is stored to the 1 three hundredths of a second and each value takes up 8 bytes of storage. The SMALLDATETIME data type can hold values between January 1st 1900 and June 6th of 2079. The time is tracked to the minute and each value takes up 4 bytes of storage. The majority of business applications can live happily with SMALLDATETIME, however, if you are in an environment where each second matters or you need to make estimates to the distant future (or past) then you have to resort to DATETIME. If you fail to specify the time when inserting a value into a DATETIME or SMALLDATETIME column, a default of midnight is used. If you fail to specify the date portion the default of January 1, 1900 is used.

Built-in Functions - Date and Time Functions

  
Date and time functions allow you to manipulate columns and variables with DATETIME and SMALLDATETIME data types.

Essential SQL Server Date, Time and DateTime Functions

  
The essential date and time functions that every SQL Server database should have to ensure that you can easily manipulate dates and times without the need for any formatting considerations at all.

Date/Time Conversions Using SQL Server

  
There are many instances when dates and times don't show up at your doorstep in the format you'd like it to be, nor does the output of a query fit the needs of the people viewing it. One option is to format the data in the application itself. Another option is to use the built-in functions SQL Server provides to format the date string for you.

Date and Time Data Types and Functions

  
The following sections in this topic provide an overview of all Transact-SQL date and time data types and functions. For information and examples that are common to date and time data types and functions

Converting Time formats

  

Hi guys,

So ive been converting an asp website to asp.net. What i did was run the migration wizard in the old VS2003, then when importing it bk into vs2008, i belive it upgraded it agin. I have been plodding along correcting basic erros and have come stuck with this one. Originallany the site has two input types, one for date and one for time. the new script is below with error im getting. I manged to get the date correct but the time is more tricky. Any suggestions would be appreciated.

 

 

Date = FormatdateTime(IIF(IsDBNull(MyRs1.Fields.Item("").Value), Nothing, MyRs1.Fields.Item("").Value), DateFormat.ShortDate)

Time = FormatdateTime(IIF(IsDBNull(MyRs1.Fields.Item("").Value), Nothing, MyRs1.Fields.Item("").Value), DateFormat.ShortTime)

 

Exception Details: System.InvalidCastException: Conversion from type 'Double' to type 'Date' is not valid.

Source Error:

Line 74: 
Line 75: 'TODO Need to correct the format 
Line 76:   Time= FormatDateTime(IIf(IsDBNull(MyRs1.Fields.Item("").Value), Nothing, MyR

Converting UTC to another Time... in XSLT SharePoint designer using ddwrt

  

I am not a programmer, nor do I know any coding languages.  I have a slightly above average understanding of how writing code works, so please keep that in mind when responding.... 

I am using a list in SharePoint; when the list is updated, I want to display "UPDATED w/ Date and Time".  I cannot figure out how to display the Local Afghanistan time vice UTC or Zulu time. I am trying to figure out what to add in to change the time displayed +4:30 hours.  I am currently using the following code in XSLT with SharePoint Designer:

<xsl:if test="ddwrt:FormatDate(string(@Modified), number(1033), 1)=ddwrt:FormatDate(ddwrt:TodayIso(), number(1033), 1)">

   <xsl:if test="ddwrt:FormatDate(string(@Modified), number(1033), 1)!=ddwrt:FormatDate(string(@Created), number(1033), 1)">

   <Span style="background-color:maroon;color:white">UPDATED <xsl:value-of disable-output-escaping="no" select="@Modified" /></span>

   </xsl:if>

(/xsl:if>


Convert Update Date/Time to VB.NET

  

Hello,

I was wondering if someone could convert this into VB.NET for me.  I'm not a PHP pro and not entirely sure what's happening here nor do I know how to get this to display the same way.

The new field is within a DetailsView, so you can give me example code as well, I'll greatly appreciate it.

Thanks.. -Jeff

PHP Code:

function updFormat($tStamp) {
	$tsTime = (strftime('%I', $tStamp) + 0) . strftime(':%M %p', $tStamp);
	
	// 86400 == # of seconds in a day
	$tsDays = floor($tStamp / 86400);
	$nowDays = floor(time() / 86400);
	if ($tsDays == $nowDays) $updTime = "Today at $tsTime";
	elseif ($tsDays == $nowDays - 1) $updTime = "Yesterday at $tsTime";
	else $updTime = strftime('%b ', $tStamp) . (strftime('%d', $tStamp) + 0) . strftime(', %Y' , $tStamp);

	return $updTime;
}
<asp:DetailsView ID="dtv_LastUpdated" runat="server" Width="100%" 
    AutoGenerateRows="False" DataSourceID="ods_MVPParcelUpdate" 
    CellPadding="1" CellSpacing="1" GridLines="None">
    <Fields>
        <asp:BoundField DataField="Update_time" HeaderText="Parcel Data:" 
            ItemStyle-HorizontalAlign="Ri

How to insert the Current date and time in to SQL Table..

  
Hello Members,              I have create the table as per the following ..create table company(  empname varchar(30),  empid int,  joindate smalldatetime)I tried,insert into table values('Kumar',202, ??????)I want to insert Current date and time into table Company.....Please give me the solution...Thanks.. 

Template for Date Time? Is this possible?

  
Hello,On my model I have a property, Birthday, of type DateTime.I need to have three Drop Down Lists: - Day- Month (Localized to Current Culture)- Year (Last 80 years including current year)How can I create a Template to for these 3 DropDownLists and then use it on my form for Birthday property?Thank You,Miguel

Converting Date to String and Formating it Linq to SQL

  
Hello I have problem in converting date to string I am using cstr(p.tripstart) , which is returning Date as 12 Aug 2010 12:AM   I need to make formating   This is my code : Dim db As New TripsLinqDataContext Dim listtrips = (From p In db.Trips _ Group Join c In db.Countries On p.TripCountryID Equals c.TripCountryID Into countrylist = Group _ Select New With {.Tripdetails = p.TripDescription + " - " + p.TripCityList + " - " + CStr(p.tripstart) + " - " + CStr(p.TripDays), _ .Tripid = p.TripID}).ToList tripboxlist.DataSource = listtrips tripboxlist.DataTextField = "Tripdetails" tripboxlist.DataValueField = "Tripid" tripboxlist.DataBind()     Regards  

how to find local time and date usin gmt time and ofset value

  
Hi All, i have to calculate local time and date in our data base i have gmt time range from 00:00 to 23:59 and offset is in same range but with + an - sign when + then we have to substract and when - then we have add and adjest the date with the given time for eg  gmt id 23:30 and ofset is +2:30 and date is '28-08-2010' then my local time is 23:30 -2:30  =21:00 and date will not change i want a cricteria like when offset get added then value should not be like 22:60 insted of this it is 23:00 and if it is exiding 24 then date should be increase and if time is - after gmt+ofset then date should be redused   Please give me a idea so i can solve it.  

Should time date stamp on .Asmx file change when project is rebuilt?

  
I have a web service project. The web service is named Recalc.asmx and it has a code behind file namedRecalc.asmx.vb Now if I make a change to my web service .vb code and then recompile the project, should my Recalc.asmx filebe updated as well? In windows explorer I see that the time date stamp of my Racalc.asmx.vb file changes to the date of the last change.But the time date stamp of the Recalc.asmx file does not change? Why is that? My original thinking was that I would simply need to copy the Recalc.asmx file to the test server. But the date stillshows it's original creation date of 7/1/2010 and not the date of the last rebuild as I was hoping it would do?
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