Solved

DATETIME SQL

Posted on 2014-09-03
8
440 Views
Last Modified: 2014-09-03
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
Comment
Question by:Yeaktom
  • 4
  • 3
8 Comments
 
LVL 15

Expert Comment

by:Vikas Garg
ID: 40301005
HI,

I think the PArameter @StartDate is declared as Date

that is why it is not returning time and just comparing dates
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40301015
What are the values for @StartDate and @EndDate?
0
 
LVL 2

Author Comment

by:Yeaktom
ID: 40301056
@Start Parameters
Available Values is set to none
Default Values set to No Default
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40301069
What value is passed to that parameter?
0
 
LVL 2

Author Comment

by:Yeaktom
ID: 40301073
Here is a screenshot
Parameter Selection
0
 
LVL 48

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40301096
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
 
LVL 2

Author Closing Comment

by:Yeaktom
ID: 40301124
Thanks Vitor!
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40301159
You are welcome. Glad I could help.

Cheers
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

829 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