Date issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Date issue

Author

Topic

musclebreast
Yak Posting Veteran

77 Posts

Posted - 2012-08-09 : 16:31:20

Hello,

maybe it's to late but I don't get it...I've got a table with the following result:

Effective_Date...................date1............Termination_Date
2012-10-03 00:00:00.000........ 30/09/ ..........30/09/2012

and this is my SQL:

select * from

myTable

WHERE convert(varchar, Termination_Date, 103) = convert(varchar, date1+substring(convert(varchar,Termination_Date,103),len(convert(varchar,Termination_Date,103))-3,4), 103) AND convert(varchar, Termination_Date, 103) >= convert(varchar, Effective_Date, 103)

I shouldn't get a result because the Effective date is larger than the Termination date...can you give me a hint?

kind regards,

Lara

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-09 : 16:46:38

whats the significance of date1 here? as per your explanation its just this


SELECT *
FROM myTable
WHERE Effective_date <= CONVERT(datetime,Termination_Date,103)

i would have made termination_date field as datetime type if it stores only datetime values to avoid unnecessary datatype conversion manipulations like above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

musclebreast
Yak Posting Veteran

77 Posts

Posted - 2012-08-09 : 17:51:52

Hi,

thanks, it works now..one little queations..if I convert both dates

to varchar

convert(varchar, Termination_Date, 103) >= convert(varchar, Effective_Date, 103)

the comparison doesn't work...is there a reason? Just to understand it..

Kind regards and thanks for your help,

Lara

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2012-08-09 : 18:09:34

Because after converting to varchar, you are now comparing strings as opposed to dates

Some days you're the dog, and some days you're the fire hydrant.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2012-08-09 : 21:51:12

quote:
Originally posted by musclebreast

Hi,

thanks, it works now..one little queations..if I convert both dates

to varchar

convert(varchar, Termination_Date, 103) >= convert(varchar, Effective_Date, 103)

the comparison doesn't work...is there a reason? Just to understand it..

Kind regards and thanks for your help,

Lara

Convert format 103 is DD/MM/YYYY, which is probably the worst format that you could chose for this.

Since strings are compared from left to right, '31/12/1900' will be greater than a date like '30/12/2012'.

This is a good illustration of why you should store dates in DATE or DATETIME columns, instead of character strings.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources