T-SQL Question. Trying to manipulate the dateTime column to only retrieve a desired date range

Question

1.00/5 (1 vote)

See more:

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 Ed

Add a Solution

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.

3 solutions

Solution 3

Hi,

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

SQL

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

Posted 23-Jan-12 2:11am

gvprabu

Solution 1

Date and Time Functions (Transact-SQL)[^]

Hope it helps

Posted 26-Jul-11 4:54am

_Zorro_

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Accepted Answer · 2011-07-26T04:58:00

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'

Posted 26-Jul-11 4:58am

OriginalGriff