Solved

DATETIME SQL

Posted on 2014-09-03
8
408 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 14

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 46

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
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40301069
What value is passed to that parameter?
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 2

Author Comment

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

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 46

Expert Comment

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

Cheers
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Calculate values in an array 18 33
Help with SQL Server Stoplist 2 18
SQL server is using more virtual memory. 5 68
recover sqlserver db 8 55
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

910 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now