Solved

Full date to midnight

Posted on 2006-11-01
5
986 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 85

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 85

Accepted Solution

by:
Mike Tomlinson earned 500 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
LINQ - C# to VB convertion 12 57
VB.Net How to Exit Sub - Exit Form??? 5 57
rebind a grid after user clicks on node in treeview 1 36
VB.Net - KeyPress Event 4 37
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

810 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