[Webinar] Streamline your web hosting managementRegister Today

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

DATETIME SQL

Good Day

Need help with DATETIME conversion in T-SQL.

Aantal as Qty,
Class as sClass,
mg as MassGrp,
Mass,
Product,
iso as Salesbrand

from bcGraderings where 

gdate >= CONVERT(DATETIME, @StartDate, 102)
 and 
 gdate <= CONVERT(DATETIME, @EndDate, 102)

Open in new window


Here is an example of the data in the GDATE field.
2014-03-14 13:08:47.000

When running this query on today's date, its not returning anything. I think its converting the date to 2014-03-14 and dropping the  time.
0
Yeaktom
Asked:
Yeaktom
  • 4
  • 3
1 Solution
 
Vikas GargBusiness Intelligence DeveloperCommented:
HI,

I think the PArameter @StartDate is declared as Date

that is why it is not returning time and just comparing dates
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
What are the values for @StartDate and @EndDate?
0
 
YeaktomAuthor Commented:
@Start Parameters
Available Values is set to none
Default Values set to No Default
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
What value is passed to that parameter?
0
 
YeaktomAuthor Commented:
Here is a screenshot
Parameter Selection
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok. You aren't providing the time only the date.
So, if you have same StartDate and EndDate (like for today) your query will be:
gdate >= '2014-09-03 00:00:00'  and gdate <= '2014-09-03 00:00:00'

Open in new window

Which means that only if you have a record with exactly '2014-09-03 00:00:00' will be returned.
Or you give tomorrows date as EndDate or you need to change your code to add one day to EndDate:
gdate >= CONVERT(DATETIME, @StartDate, 102)
 and 
 gdate <= CONVERT(DATETIME, @EndDate, 102)+1

Open in new window

0
 
YeaktomAuthor Commented:
Thanks Vitor!
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You are welcome. Glad I could help.

Cheers
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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