comparing dates

  • 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

  • Try this .DATEPART

    --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
    🙂

  • 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)...

  • 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?

  • 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))

  • 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.

  • 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

  • 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

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

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