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!
johnqtrAsked:
Who is Participating?
 
rafranciscoConnect With a Mentor Commented:
Try changing the date to varchar, like this:

"select Lots.LotID as 'ID', Lots.LotNumber as 'Lot Number', CONVERT(VARCHAR(10), 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) & "%'"
0
 
Brian CroweDatabase AdministratorCommented:
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.
0
 
ispalenyCommented:
You remove time from datetime by combined deterministic function
CONVERT(datetime,CONVERT(char(8),YourDateTimeColumn,112),112)
0
 
johnqtrAuthor Commented:
Tried first post, ran great!  Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.