Click here to Skip to main content
12,403,391 members (71,401 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: TSQL
In SQL SERVER 2005/2008 I am trying to retrieve a certain range of dates. I tried to convert the DateTime datatype to a numeric and a decimal using CONVERT, but this did not work. It gave me a ficticous number. How would you manipulate the DateTime datatype to focus in on a desired range of dates using T-SQL????
Posted 26-Jul-11 4:52am
SQL Ed359
Comments
_Zorro_ 26-Jul-11 10:54am
   
I probably am missing something, but why would you want to convert a DateTime to Decimal ?!
SQL Ed 26-Jul-11 12:00pm
   
The question clearly states more than one time that I am trying to retrieve a desired range of dates.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Why convert it at all? If you have a DateTime, use it as a DateTime!
SELECT * FROM myTable WHERE dateEntered < '2011-05-01'
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

Hi,

When ever checking date time column, U need to convert "112" format. check the following example.

DECLARE @DateTime TABLE(ID INT IDENTITY(1,1),EmpName VARCHAR(100),DOJ DATETIME )
DECLARE @FDate DATETIME,@TDate DATETIME
 
INSERT INTO @DateTime(EmpName,DOJ)
SELECT 'Kumar',GETDATE()
UNION ALL
SELECT 'Raja',GETDATE()-10
UNION ALL
SELECT 'Sive',GETDATE()-50
UNION ALL
SELECT 'Venkat',GETDATE()-100
 

SELECT @FDate=GETDATE()-30,@TDate=GETDATE()
SELECT @FDate,@TDate
SELECT * FROM @DateTime 
WHERE CONVERT(VARCHAR(10),DOJ,112) BETWEEN CONVERT(VARCHAR(10),@FDate,112) AND CONVERT(VARCHAR(10),@TDate,112)

In this example the DateTime Column values like "2012-01-23 18:32:59.960" is Converted to "20120123" (We will do this as Number or Varchar).
this format 112 is gives you as 2012 year , 01 month, 23 day (20120123).

With Regards,
GVPrabu
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160721.1 | Last Updated 4 Feb 2013
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100