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


comparing dates


comparing dates

Author
Message
river1
river1
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11530 Visits: 1741
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
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3722 Visits: 1139
Try this .DATEPART

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

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

Group: General Forum Members
Points: 11530 Visits: 1741
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
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11530 Visits: 1741
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
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12424 Visits: 2899
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 Guru
SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)

Group: General Forum Members
Points: 159112 Visits: 11668
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
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4083 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 (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)

Group: General Forum Members
Points: 226535 Visits: 40444
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