asked on

Removing the Time from query result...

I have this query which I've done in VB.NET:

"select Lots.LotID as 'ID', Lots.LotNumber as 'Lot Number', Lots.DateShipped as 'Shipped', Lots.Analyst as 'Analyst', Lots.PkgCode as 'Pkg', Lots.LotWeight as 'Weight', Lots.CustomerNumber as 'Customer' from Lots where Lots.LotNumber Like '" & Left(lotNumber.SelectedItem.Text, 4) & "%" & Mid(lotNumber.SelectedItem.Text, 6, 9) & "%'"

When the column "Shipped" is made, it shows the time 12:00:00AM. What code (using the query above) do I use to remove the time and just display the date?

Thanks in advance!

ASKER CERTIFIED SOLUTION

rafrancisco

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Brian Crowe

you should typically do this on the client side but...

"select Lots.LotID as 'ID', Lots.LotNumber as 'Lot Number', CONVERT(varchar, Lots.DateShipped, 101) as 'Shipped', Lots.Analyst as 'Analyst', Lots.PkgCode as 'Pkg', Lots.LotWeight as 'Weight', Lots.CustomerNumber as 'Customer' from Lots where Lots.LotNumber Like '" & Left(lotNumber.SelectedItem.Text, 4) & "%" & Mid(lotNumber.SelectedItem.Text, 6, 9) & "%'"

this converts your date to a string which may or may not be acceptable.

ispaleny

You remove time from datetime by combined deterministic function
CONVERT(datetime,CONVERT(char(8),YourDateTimeColumn,112),112)

johnqtr

ASKER

Tried first post, ran great! Thank you!