[Webinar] Streamline your web hosting managementRegister Today

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

Select records based on GetDate()

I assume the reason why the select doesn't find any records is because it's looking at the time also?
update Top (3000) Order set CreateDate = GetDate()
where CreateDate = '2007-11-30 22:48:32.063'
 
select * from Order where  CreateDate = GetDate()

Open in new window

0
dba123
Asked:
dba123
1 Solution
 
mdefalcoCommented:
Yeah, I think since the value of GetDate has changed already.

How about;
Dim strDate
strDate = GetDate()
 
update Top (3000) Order set CreateDate = strDate
where CreateDate = '2007-11-30 22:48:32.063'
 
select * from Order where  CreateDate = strDate

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>I assume the reason why the select doesn't find any records is because it's looking at the time also?
yes, the second getdate() will actually return a new value, only slightly different than the update..

here is the corrected code:
DELCARE @d DATETIME 
SET @d = getdate()
update Top (3000) [Order] 
   set CreateDate = @d
where CreateDate = convert(datetime, '2007-11-30 22:48:32.063', 120)
 
   select * 
     from [Order] 
    where CreateDate = @d

Open in new window

0
 
ursangelCommented:
yeah, thats true. Getdate() will always provide you with instant date and time values. each instant the datetime value will be different from teh previous since the time stmp is attached with the value.

Rest you can use angelll's query. that is save the getdate() value to a variable and then try updating the table value with it and then retrieve using the same value.

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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