comparing dates

Question

comparing dates

river1

SSC-Insane

Points: 24309
More actions
June 19, 2014 at 4:52 am

#396937

Hi,
I have this t-sql
CONVERT(VARCHAR(10),dt_inicio,105) >= CONVERT(VARCHAR(10),'01-01-2013',105)
the comparation is not correct because I'm comparing strings.
I would like to compare the day, month and year of the two dates.
How can I do this?
thank you

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

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

rhythmk SSCertifiable Points: 7271 More actions · Answer 1

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂

river1 SSC-Insane Points: 24309 More actions · Answer 2

I tried this:

convert(datetime,CONVERT(VARCHAR(10),dt_inicio,105),105) >= '01-01-2013'

AND convert(datetime,CONVERT(VARCHAR(10),dt_inicio,105),105)<= '14-03-2013'

but get the message:

can't convert char data type to datetime (out of range)...

river1 SSC-Insane Points: 24309 More actions · Answer 3

The data exist in the database in this format:

2010-09-23 13:02:54.000

I want to compare with a string that I get from a application:

01-02-2013

The hours , minuts and seconds are not importante.

Can someone help?

Umer Akram SSC-Insane Points: 22209 More actions · Answer 4

you need to convert your comparison dates instead of converting the date in the table

Like this:

declare @a varchar(12) = '14-03-2013'

select

@a, convert(date,substring(@a,charindex('-',@a)+1,2)+'/'+

left(@a,charindex('-',@a)-1)+'/'+

right(@a,charindex('-',reverse(@a))-1))

Ed Wagner SSC Guru Points: 287026 More actions · Answer 5

Lynn Pettis has a page with some common date routines posted at http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/ that will help give you the syntax you're after in an efficient way without having to parse out each part of the date.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 6

If you are using sql server edition higher than 2005 you can simply convert it to date datatype and it will return just the datepart

select convert(date,'2010-09-23 13:02:54.000')

else you will have to rely on those cumbersome string manipulations to get the datepart

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

river1 (6/19/2014)
Hi,
I have this t-sql
CONVERT(VARCHAR(10),dt_inicio,105) >= CONVERT(VARCHAR(10),'01-01-2013',105)
the comparation is not correct because I'm comparing strings.
I would like to compare the day, month and year of the two dates.
How can I do this?
thank you

If the data type of the column dt_inicio is datetime why not just do this:

...

WHERE

dt_inico >= '20130101' -- the string value '20130101' will be implicitly converted to a date/time value of 2013-01-01 00:00:00.000