Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

comparing dates Expand / Collapse
Author
Message
Posted Thursday, June 19, 2014 4:52 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, August 14, 2014 9:46 AM
Points: 743, Visits: 1,055
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
Post #1583709
Posted Thursday, June 19, 2014 5:03 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 11:45 PM
Points: 562, Visits: 887
Try this .DATEPART


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

http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1583718
Posted Thursday, June 19, 2014 5:07 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, August 14, 2014 9:46 AM
Points: 743, Visits: 1,055
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)...
Post #1583722
Posted Thursday, June 19, 2014 5:15 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, August 14, 2014 9:46 AM
Points: 743, Visits: 1,055
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?
Post #1583728
Posted Thursday, June 19, 2014 5:32 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 3:32 AM
Points: 682, Visits: 1,226
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))

Post #1583737
Posted Thursday, June 19, 2014 5:54 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 10:50 AM
Points: 4,139, Visits: 3,169
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
Post #1583745
Posted Thursday, June 19, 2014 8:25 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 3:09 AM
Points: 314, Visits: 2,530
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
Post #1584135
Posted Thursday, June 19, 2014 11:18 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:43 AM
Points: 23,215, Visits: 31,895
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





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)
Post #1584152
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse