asked on

Convert SQL Column DataType Int to Date Field

I have Column Field :- FillDate
DataType of this Column is Integer

Value is stored in the Table as Integer.
for which i already written a query to represent YYYY-MM-DD Format.

SELECT
case when len(Rx.FillDate) = 7 then dateadd(d, (cast(RIGHT(Rx.FillDate,3) as int) -1), cast(LEFT(Rx.FillDate,4) as DATE))else null end as [FillDate]
FROM Rx

Being Int DataType in the Table, I am not able to sort or write a Date based filteration.

If i need records between 10/01/2010 to 11/01/2010 .. some thing like date filteration , how should i do this ?

sammySeltzer

You should have left it like it you had - mmddyyyy and then you would do something like this:

between convert(varchar, startdate, 101) and convert(varchar, enddate, 101)

I am sure some of the gurus can come up with something better.

ASKER CERTIFIED SOLUTION

cyberkiwi

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Scott Pletcher

It looks as if your data is in format yyyyddd (right?).

You really shouldn't adjust a table column, you should adjust the data outside the column. Otherwise SQL can't use an index even if one exists.

That doesn't mean you can't specify your ranges as normal dates. Just let code adjust it to the right table column format for you.

For example:

DECLARE @startdate datetime
DECLARE @enddate datetime

SET @startdate = '20101001'
SET @enddate = '20101101'

SELECT ...
FROM Rx
WHERE FillDate >= YEAR(@startdate) * 1000 + DATEPART(DAYOFYEAR, @startdate) 
    AND FillDate < YEAR(@enddate) * 1000 + DATEPART(DAYOFYEAR, @enddate)

Open in new window

chokka

ASKER

Scott, Thank you.

Cyberkiwi solution also worked. I have not noticed your syntax.

Scott Pletcher

Never pays to actually explain an answer here :-) .

Scott Pletcher

Btw, always use:
yyyymmdd
instead of:
yyyy-mm-dd
whenever you can, because SQL *always* interprets yyyymmdd correctly, but yyyy-mm-dd can cause errors with certain SQL settings.

cyberkiwi

whenever you can, because SQL *always* interprets yyyymmdd correctly, but yyyy-mm-dd can cause errors with certain SQL settings.

Scott, I don't know where you come from, but that is not true, as far as I know.

Scott Pletcher

What makes you say that?

YYYY-MM-DD will clearly not work with "SET DATEFORMAT ydm".

But I think there are a couple of other settings that cause it problems.

Also, it's potentially ambiguous and could give you bad data if the setting is actually ydm and you think it's ymd:

SELECT '2010-03-07'

Is that Mar 7 or Jul 3?? It depends on what country you're in :-) .

20100307 is *always* Mar 7.

Scott Pletcher

I swear I once saw some kind of odd errors people got with yyyy-mm-dd even with a (default) ymd setting.

It's my understanding SQL always treats yyyymmdd as exactly that.

Is there some type of setting I'm overlooking on that?

cyberkiwi

sold

Scott Pletcher

CORRECTION:

I swear I meant to put a smiley face after this:

YYYY-MM-DD will clearly not work with "SET DATEFORMAT ydm". :-) :-)