how to compare the dates in different format with the getdate() in sql server

Question

I have a column, where the date values are stored in nvarchar data type. The date format is dd-mm-yy, dd.mm.yy

Currently I am replacing the - with . and comparing with get date, as all dates formats used in other tables are in dd.mm.yyyy format.

Now consider a scenario where the dates may be in other formats like dd/mm/yyyy or other date formats, is there any way to check whether the date is valid and compare it with getdate()

Also I find few unknown records are also available like 57937234, I need to ignore them.

As of now I am using this query and it is working fine:

select 
    A.Value, replace( B.Value,'-','.') 
from 
    smpltbl as A
INNER JOIN 
    smpltbl as B ON A.MSKEY = B.MSKEY
INNER JOIN 
    smpltbl as tbl_status ON A.MSKEY = tbl_status.MSKEY
where 
    A.colname = 'XYZ' 
    AND B.colname = 'ABC' 
    AND tbl_status.colname = 'Status' 
    AND tbl_status.Value = 'OK'
    AND B.Value <> '00.00.0000' 
    and B.Value <>'..' 
    AND replace( B.Value,'-','.') < cast(convert(nvarchar, GETDATE(), 104)

Can someone kindly help me in accepting the dates in different format an comparing with the system date and the result must be displayed in DD.MM.YYYY format itself

Be advised that anything and everything you do here is going to be a hacky workaround. Make sure you have a plan to fix the root cause wherein you store your dates as **dates** (the correct data type for the field) in the future (and then clean up the old data). — alroc, Nov 13 '14 at 04:02
[Bad habits to kick : choosing the wrong data type](https://sqlblog.org/2009/10/12/bad-habits-to-kick-choosing-the-wrong-data-type) - you should always use the most appropriate data type - that's what they're there for, after all! If it's a date - **store is as a `DATE`** ! — marc_s, Nov 13 '14 at 05:59
What formats *might* you have to deal with? If there's any possibility that some have been stored as `mm/dd/yyyy` (or other equivalents) then you should recognize that you may have a worse problem - the dates might *validate* but you won't know how to correctly interpret them. — Damien_The_Unbeliever, Nov 13 '14 at 15:35

score 0 · Answer 1 · answered Nov 13 '14 at 03:03

0

Depends on the version of SQL Server you are using you can have different solutions.

CONVERT(VARCHAR(10), GETDATE(), 103)

answered Nov 13 '14 at 03:03

sqluser

5,502
7
36
50

when we use 103 would return dd/mm/yyyy. which ever format the date may be stored in like dd/mm/yyyy or dd.mm.yyyy or dd-mm-yyyy. how can i compare and display the results in dd.mm.yyy . when i am using the below its displaying all the records. not records which are less than system date. where cast(LEFT(date_b,2)+'.'+SUBSTRING(date_b,4,2)+ '.'+ RIGHT(date_b,4) as nvarchar(400)) < cast(convert(nvarchar(400),GETDATE(),104) as nvarchar(400)) – Deva Prakash Budati Nov 13 '14 at 03:29

score 0 · Answer 2 · edited Nov 13 '14 at 14:51

0

cast((right(date_b,4)+'-'+substring(date_b,4,2)+'-'+left(date_b,2)+' 00:00:00.000') as datetime) 
<cast(convert(nvarchar,getdate(),104) as datetime)

When i used the above comparison statement, my expected and actual result are same.

edited Nov 13 '14 at 14:51

Andriy M

76,112
17
94
154

answered Nov 13 '14 at 14:10

Deva Prakash Budati

3
2
5

score 0 · Answer 3 · edited May 23 '17 at 12:24

As have been said in the comments, the right way to do this would be to use the appropriate data type (date or datetime). But if you have to work with dates as strings, you could at least do things more simply, like this for instance:

CONVERT(date, date_b, 104) < CONVERT(date, GETDATE())

If you are using SQL Server 2005 or earlier version:

CONVERT(datetime, date_b, 104) < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)

The right part of the comparison in the last example is a known method of truncation of a datetime value.

how to compare the dates in different format with the getdate() in sql server

3 Answers3