SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


comparing dates


comparing dates

Author
Message
river1
river1
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4398 Visits: 1366
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
rhythmk
rhythmk
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1694 Visits: 1099
Try this .DATEPART

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

http://www.sqlservercentral.com/articles/Best+Practices/61537/
:-)
river1
river1
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4398 Visits: 1366
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
river1
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4398 Visits: 1366
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?
twin.devil
twin.devil
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5284 Visits: 2692
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
Ed Wagner
SSC-Forever
SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)

Group: General Forum Members
Points: 48057 Visits: 10844
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
Sachin Nandanwar
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1539 Visits: 2633
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
Lynn Pettis
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94037 Visits: 38955
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




Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search