Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Full date to midnight

Posted on 2006-11-01
5
Medium Priority
?
1,016 Views
Last Modified: 2008-01-09
Im pulling the date from a sql database it has the date and time I want to keep the date and make the time 00:00:00.000 AM

here it is in SQL i need it converted to vb.net, x.item(4) is the date from the database
I'm trying to get the minutes itno the day.

datediff(minute, CAST(CONVERT(varchar(10), DATEADD(dd, 0, x.Item(4)), 101) + ' 00:00:00.000 AM' AS DATETIME), x.Item(4))
0
Comment
Question by:Millkind
  • 2
  • 2
5 Comments
 
LVL 19

Expert Comment

by:VoteyDisciple
ID: 17851917
What do you mean "get the minutes into the day"?  I'm guessing you're trying to figure out how many minutes after midnight a certain time is -- for example, 8:30 am would be 510 minutes.

I don't know what DBMS you're using, but in MySQL this will work:

TIME_TO_SEC(
    TIMEDIFF(
        datetime_field,
        TIMESTAMP(DATE(datetime_field)) -- Convert NOW() to a DATE, then back to a DATETIME, truncating the time, basically
    )
) / 60

(There is no TIME_TO_MIN, but the last bit of division isn't exactly taxing.  (-:  )
   
0
 
LVL 86

Expert Comment

by:Mike Tomlinson
ID: 17852190
"Im pulling the date from a sql database it has the date and time I want to keep the date and make the time 00:00:00.000 AM"

Convert your value from the database to a DateTime instance.  Then make a NEW DateTime instance using the Year, Month and Day from that value but setting the Hour, Minute and Second values to zero:
 
        Dim origDt As DateTime = CType(x.Item(4), DateTime)
        Dim dt As New DateTime(origDt.Year, origDt.Month, origDt.Day, 0, 0, 0)
        Debug.Print(dt.ToString)
0
 

Author Comment

by:Millkind
ID: 17852255
DateDiff(DateInterval.Minute, x.Item(4).date, x.Item(4))

actually this seems to be working just fine.  i was messing with it.  does it look okay?
0
 
LVL 86

Accepted Solution

by:
Mike Tomlinson earned 2000 total points
ID: 17852433
You can also use the TimeSpan class:

    Dim ts As TimeSpan = x.Item(4).Subtract(x.Item(4).Date)
    Debug.Print(ts.TotalMinutes)

You can get Hours, Minutes, Seconds, etc...
http://msdn2.microsoft.com/en-gb/library/system.timespan_members.aspx
0
 

Author Comment

by:Millkind
ID: 17852533
that works great
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

876 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