between dates

[jeffinperth]

Hi All,
Have read BOL and I think I'll need to use cast or convert, but i still can't get my head round this.
in oracle we have a varchar field that contains a period name in the format 'MON-YY' , i can use the following

where to_date(b.period_name,'Mon-yy') between to_date('Jan-03','Mon-yy') and to_date('Feb-03','Mon-yy')

Could somebody please help me out with the TSQL equivalent, if the table was imported into SQL Server 2000.

Regards

Jeff

 

[ESquared]

First of all, I encourage you to change the field to an actual datetime field, but I suppose that's up to you. If you have a *very* large table then I strongly recommend it.

To work with it as is,

WHERE convert(datetime,left(@n,3) + ' 01 ' + right(@n,2)) BETWEEN '2004-01-01' AND '2004-02-29'

 

[jeffinperth]

Hi ESQ,
when i try this

SELECT h.JE_CATEGORY , h.je_source , l.period_name , l.entered_dr , l.entered_cr
FROM GL_JE_LINES l , GL_JE_HEADERS h , gl_code_combinations gl
where l.JE_HEADER_ID = h.JE_HEADER_ID and
l.code_combination_id=gl.code_combination_id and
gl.segment1 = '800' and
gl.segment2 = '86029' and
gl.segment3 = '760154' and
convert(datetime,left(l.period_name,3) + ' 01 ' + right(l.period_name,2)) BETWEEN '2004-01-01' AND '2004-02-29'

i get this error
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

the sql works when i don't try the date filter . . .

could you tell me what i'm doing wrong?

regards

jeff

 

[ESquared]

Can you give a sample of what the data in l.period_name looks like? Is the year ever 1 digit instead of 2? I tried my answer on my own server before posting and it worked fine.

Hmmm, do this in query analyzer:

DECLARE @d varchar(10)
SET @d = 'Jan-03'
SELECT convert(datetime,left(@d,3)+ ' 01 ' + right(@d,2))

and tell me what happens.

 

[jeffinperth]

Hi again . . .

the data looks like . .

Jan-00
Jan-01
Jan-02
Jan-03
Jan-04
Feb-00
Feb-01

etc etc

when i pasted your sugestion into query analyzer, i got the response

2003-01-01 00:00:00.000

hope this helps ( both u & me !!! )

Jeff

 

[ESquared]

SELECT * FROM GL_JE_LINES
   WHERE
      Left(period_name,3) Not In ('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')
      OR Not IsNumeric(Right(period_name,2))

-- and if that gives no records try

SELECT * FROM GL_JE_LINES
   WHERE
      Convert(int,Right(period_name,2))<0

 

[jeffinperth]

hi esquared,
I think i'll take your qadvice and change the date to a true date field, this is getting bigger that ben hur .

thanks for all you help

Jeff

 

[ESquared]

Well, before you do it, you might want to run the query I gave you.

First of all, you have to use something like it to convert them to dates, anyway.

Second, I have a strong suspicion that you have a malformed date in that field, anyway, and you'll corrupt your data if you force it to datetime anyway.