Trouble with Dates in SQL

Question

I'm having some trouble with dates in an SQL query. I want a select query where one date is further on than another, so this is what I started out with:

SELECT * FROM TableA WHERE (DATE1 < DATE2);

This returned some pretty inconsistent results. I believe the problem lies in the fact that the dates are NVARCHAR() in the format 21/09/2006 13:28:06 and have read that there can be difficulties with dates not stored in the DATETIME format.

So then I tried this

SELECT * 
FROM Table A 
WHERE (CAST(DATE1 AS DATETIME) < CAST(DATE2 AS DATETIME))

Unfortunately this left me with the following error:

Conversion failed when converting date and/or time from character string

Whilst trying to research this error, I came across a potential solution

SELECT * 
FROM Table A 
WHERE (CONVERT(DATETIME,DATE1,112) < CONVERT(DATETIME,DATE2,112))

But I then got this error:

The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value

Could someone please tell me where I am going wrong with either of these two approaches or something that will actually work, I would appreciate it.

Many thanks

Answer 1

You may also have bad data in your table that can't convert. This is why you should NEVER store dates in a varchar or nvarchar field because it allows bad dates to get entered. It is especially complicated when you use dates that are not the standard US date formats. Because you are using a non-US format, it is important to understand the results you would get when trying to convert various formats and why some values cannot be made to work.

Run the queries below and see if you can start to understand what is happening and how to convert your data correctly.

create table #temp (textdate varchar(50), realdate datetime)

insert into #temp(textdate)
values ('20140709'),('ASAP'),('01/13/2013') , ('21/09/2006 13:28:06')

select * from #temp
update #temp
set realdate = textdate
where textdate = '20140709'

update #temp
set realdate = textdate
where textdate = '01/13/2013'


update #temp
set realdate =textdate
where textdate ='21/09/2006 13:28:06' 

update #temp
set realdate =CONVERT(DATETIME,textdate,103)
where textdate ='21/09/2006 13:28:06' 

update #temp
set realdate = textdate
where textdate = 'ASAP'

update #temp
set realdate = null

SET DATEFORMAT dmy;

update #temp
set realdate = textdate
where isdate(textdate) = 1

select * from #temp

Note that what will work to convert on type of entry may not work for other entries. If you are sure that all are in dmy format (or the stardard ymd that is unversally recognized), then the set dateformat statement may help you. It will not work however if you have values that are mixed date formats or values that are simply not dates like "ASAP' in the example above. Other values that might not covert in your format '30/02/2014'. It looks like a date but Feb does not have 30 days even in leap years. Data like this can easily get into a varchar field which would not reject the imput like a date field woudl have. It is highly likely you will need to clean out some data which is simply not a date. Depending on your needs and what teh date field is intended to be used for and whether the data is required you may need to have someone find out the real date and fix the data or you may be able to simply null out the field where it has stored things that are not dates.

Answer 2

The default DATETIME format is the following:

yyyymmdd HH:MM:SS

You should use CONVERT(DATETIME,DATE1,103)

Or my original dirty solution, which can be handy if your format is not supported:

Right now you have the format: mm/dd/yyyy HH:MM:SS so all you need to do is to create a few conversions with:

SUBSTRING(DATE1, 7, 11) + SUBSTRING(DATE1, 1, 2) + 
   SUBSTRING(DATE1, 4, 6) + SUBSTRING(DATE1, 12, 20)

so at the end:

 CAST((SUBSTRING(DATE1, 7, 11) + SUBSTRING(DATE1, 1, 2) + SUBSTRING(DATE1, 4, 6) + SUBSTRING(DATE1, 12, 20)) AS DATETIME)

will give the result you want.