Solved

Full date to midnight

Posted on 2006-11-01
5
980 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
Comment Utility
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
Comment Utility
"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
Comment Utility
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
Comment Utility
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
Comment Utility
that works great
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
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…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now