Solved

Full date to midnight

Posted on 2006-11-01
5
991 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

752 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