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

Select with DateTime where condition

Hi,

I need to select any record after a given date ...
The where filed (TimeStamp)is typeof datetime - how can I do my query?
SELECT [ID]
      ,[Timestamp]
  FROM [dbo].[AllRecords] WHERE [Timestamp]>2011-06-30 23:59:59.000

does not work ...

Thanks

Andre
0
andre72
Asked:
andre72
  • 4
  • 2
1 Solution
 
Rajkumar GsSoftware EngineerCommented:
DECLARE @Date DATETIME
SET @Date = '2011-06-30 23:59:59.000'
SELECT [ID]
      ,[Timestamp] FROM [dbo].[AllRecords] WHERE [Timestamp > @Date

Open in new window


Raj
0
 
Rajkumar GsSoftware EngineerCommented:
If you mean to want to fetch those records with date which is after the date you mention, you can try this query

DECLARE @Date DATETIME
SET @Date = '2011-06-30 23:59:59.000'
SELECT [ID]
      ,[Timestamp]
  FROM [dbo].[AllRecords] WHERE DATEDIFF(d,@Date , [Timestamp])> 0

Open in new window


Raj
0
 
andre72Author Commented:
I get an error like before:
While converting from varchar datatyp to datetime datatype a value is outside
The conversion of a varchar data type to a datetime data type of the value is out of range. (translated from German)

But Timestamp is for sure datetime ...
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
Rajkumar GsSoftware EngineerCommented:
I think the date format is different.

Try this line as the first line
 SET DATEFORMAT MDY

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you should read this article:
http://www.experts-exchange.com/A_1499.html

so, above suggestion is almost fine, but still implicit data type conversion:
SET @Date = '2011-06-30 23:59:59.000'
should be:
SET @Date = convert(datetime, '2011-06-30 23:59:59.000', 120)

hope this helps
0
 
andre72Author Commented:
Strange you got it ...
0
 
Rajkumar GsSoftware EngineerCommented:
Glad I could help you
Raj
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

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