Click here to Skip to main content
Rate this: bad
good
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 5:52am
SQL Ed359
Comments
_Zorro_ at 26-Jul-11 10:54am
   
I probably am missing something, but why would you want to convert a DateTime to Decimal ?!
SQL Ed at 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
0 OriginalGriff 240
1 Kamal Rocks 184
2 BillWoodruff 173
3 PIEBALDconsult 160
4 CPallini 155
0 OriginalGriff 5,695
1 DamithSL 4,506
2 Maciej Los 4,007
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,180


Advertise | Privacy | Mobile
Web03 | 2.8.141216.1 | Last Updated 4 Feb 2013
Copyright © CodeProject, 1999-2014
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