• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1027
  • Last Modified:

CONVERT(varchar(10), GETDATE(), 101) question

WHERE     (DateTime >= CONVERT(varchar(10), GETDATE() - 1, 101)) AND  (DateTime < CONVERT(varchar(10), GETDATE(), 101)

DateTime = DBSMALLDATE

This gives me all records of the day before .. now i want all the records of the day before .. but only between a certain time. Notation: 2/7/2009 8:00:00 PM

Thanks in advanced
0
Amien90
Asked:
Amien90
  • 2
  • 2
1 Solution
 
reb73Commented:
Something like this perhaps -

You could adapt it to suit your filter requirements..
DECLARE @starttime varchar(12), @endtime varchar(12)
SELECT   @starttime = '08:00:000 PM'
	,@endtime   = '09:00:000 PM'
 
SELECT   COMPARISONFROM = DATEADD(d, 0, datediff(d, 0,getdate() - 1)) + cast(@starttime as datetime)
	,COMPARISONTO   = DATEADD(d, 0, datediff(d, 0,getdate() - 1)) + cast(@endtime as datetime)

Open in new window

0
 
Amien90Author Commented:
not something like:

DateTime >= CONVERT(varchar(10), GETDATE() - 1, 101) + ' 9:00:0000 PM'
0
 
reb73Commented:
Then you need to cast the entire result into a datetime like -

DateTime >= CONVERT(datetime, CONVERT(varchar(10), GETDATE() - 1, 101) + ' 9:00:000 PM')

0
 
Amien90Author Commented:
CONVERT(varchar(10), GETDATE() - 1, 101) + ' 7:00:000 AM')  .. Works

why 2x Convert?
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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