[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 174
  • Last Modified:

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!
0
johnqtr
Asked:
johnqtr
1 Solution
 
rafranciscoCommented:
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 CroweCommented:
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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now