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


comparing dates


comparing dates

Author
Message
river1
river1
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1768 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
SSC Eights!
SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)

Group: General Forum Members
Points: 912 Visits: 1078
Try this .DATEPART

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

http://www.sqlservercentral.com/articles/Best+Practices/61537/
:-)
river1
river1
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1768 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
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1768 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
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2361 Visits: 2673
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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16665 Visits: 10070
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
Say Hey Kid
Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)

Group: General Forum Members
Points: 685 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-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39461 Visits: 38552
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