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

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

SQL DATETIME Compare HELP



Hello Experts,

I am trying to fetch all records created after or equal to a specific record create date. I am using following query but it is not giving me correct results .I am only interested in compareing dates till hours and not beyond that.
Please let me know how should I rewrite query to get desired result

Thanks
Select *
From Master tm
Where
tm.CreateDate >= (Select CreateDate From Master Where MasterID = @MasterId)
0
EmiliJ
Asked:
EmiliJ
1 Solution
 
Nathan RileyFounder/CTOCommented:
Here you go:
Select *
From Master tm
Where
tm.cast(CreateDate as datetime) >= (Select cast(CreateDate as datetime) From Master Where MasterID = @MasterId)

Open in new window

0
 
Nathan RileyFounder/CTOCommented:
Correction:
Select *
From Master tm
Where
cast(tm.CreateDate as datetime) >= (Select cast(CreateDate as datetime) From Master Where MasterID = @MasterId)

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
"I am only interested in compareing dates till hours and not beyond that. "

Can you give an example of what you meant by this statement with the actual data and expected results.  My interpretation is that you want to get date from a specific @MasterId and then grab all the records that were created in the same hour or later.

E.g. selected date is "2009-04-21 14:31"

You want:
...
WHERE CreateDate >= '2009-04-21 14:00'

Is that correct?
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
GSQLCommented:
Not sure what you mean, but if you want to select records from Master where Master.CreateDate is greater than or equal to the hour of the CreateDate for the @MasterId record, try

Select *
From Master tm
Where
tm.CreateDate >= (select left(convert(nvarchar, tm2.CreateDate, 121), 13)+':00:00.000' from Master tm2 where tm2.MasterId = @MasterId)

0
 
Kevin CrossChief Technology OfficerCommented:
If I am correct, please try this:
Select *
From Master tm
Where
tm.CreateDate >= (
	Select DATEADD(hour, DATEPART(hour, CreateDate), DATEDIFF(day, 0, CreateDate)) 
	From Master 
	Where MasterID = @MasterId
)

Open in new window

0
 
Sreedhar VengalaCommented:
Try this:


SELECT *
FROM [Master] AS [M0] 
WHERE [M0].[CreateDate] >= (( 
SELECT TOP (1) [M!].[CreateDate] 
FROM [Master] AS [M1] 
WHERE [t1].[MasterID] = @MasterID))

Open in new window

0
 
kavintCommented:
Try this :

@compareDate is your input date against which you are comparing the createDate of all records.

Select *
From Master
Where
datediff(hh,createdate,@compareDate) >= 0
and MasterId = @masterId
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.

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