Dates, Dates and More Dates

Question

Post reply

Dates, Dates and More Dates

john.imossi

SSC Rookie

Points: 30
More actions
August 28, 2009 at 5:24 am

#137696

I've recently moved from Oracle SQL Development to SQL Server and I'm having a little trouble when creating queries using dates. I would be very grateful for any help anyone could give me. Here's the problem:
In Oracle, when using a date range for a query I would right:
SELECT *
WHERE
SAMPLE_DATE BETWEEN TO_DATE('01-JAN-2009 00:00:00', 'DD-MON-YYYY HH24:MI:SS')
AND TO_DATE('31-DEC-2009 00:00:00', 'DD-MON-YYYY HH24:MI:SS')
Of course this expression will not work within SQL Server as there is no TO_DATE. However does anyone know how I would go about making an expression like this in SQL Server?
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

davidandrews13 SSCertifiable Points: 6809 More actions · Answer 1

select * from table1 where SomeDate between '2009-01-01 00:00:00' and '2009-08-28 23:59:59'

is something like this what your looking for?

john.imossi SSC Rookie Points: 30 More actions · Answer 2

Yes thank you, something like that. However when I do this:

Sample_date BETWEEN '01/06/09 00:00:00' AND '30/06/09 23:59:59'

I get this as an error message

"The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value."

So I must need to convert it somehow and that's what I don't know how to do.

Thanks

davidandrews13 SSCertifiable Points: 6809 More actions · Answer 3

i'd do my dates as :

between '2009-06-01' and '2009-06-30'

or

'01 jun 2009' and '30 jun 2009'

not

'01/06/2009' and '30/06/2009'

or

'01/06/09' and '30/06/09'

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 4

davidandrews13 (8/28/2009)
select * from table1 where SomeDate between '2009-01-01 00:00:00' and '2009-08-28 23:59:59'
is something like this what your looking for?

Try this instead:

SELECT *

FROM table1

WHERE SomeDate BETWEEN CAST('2009-01-01 00:00:00' AS smalldatetime)

AND CAST('2009-08-28 23:59:59' AS smalldatetime)

That way all the data types are forced to be the same and you're not running into implicit conversion errors.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

john.imossi SSC Rookie Points: 30 More actions · Answer 5

Thank you very much for your help, I will try that out. Again thanks for the help and for replying so quickly as well.

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 6

Usually you should provide dates in ISO or ODBC style in TSQL. If you want to use British date format you have to use CONVERT with style information or SET DATEFORMAT

Greets

Flo