Solved

Full date to midnight

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
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…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

830 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