Compare two Date in ddMMyy format

Question

Hello,

there is a date column in a table (like s_Date)  that save ddMMyy date as string format.  like '311112' 

i have a query like this :

SELECT *  FROM [MyTable] WHERE [s_Date]  >=  T1  AND [s_Date]  <=  T2 ;

T1 and T2 are in date format as ddMMyy. 

I know that my query is wrong. what should i do?

thanks.

Answer 1

When your column uses one of the date data types like DATE or DATETIME, then the format doesn't matter. Your problem looks like you specify a date literal in a uncommon format. The most common format for a date literal is ISO 8601, thus in your case '2012-11-31'. Another choice could be the ODBC format. Both are independend of all system settings. See String Literal Date and Time Formats.

Answer 2

If you want to use date only, then the best format is yyyymmdd (no -).

Check this blog post, it explains it very well:

The ultimate guide to the datetime datatypes

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 3

there is two problem :

1- i can not change table data , so my date format  is 'ddMMyy' for ever!

2- i need to know how many data exists between two dates like : '101211' - '010312'

what can i do?

thanks.

Answer 4

Who developed this table to store date in such format?

Check this blog post

Dear ISV: You’re Keeping Me Awake Nights with Your VARCHAR() Dates. Hopefully this article is strong enough to convince you to never use varchar for dates.

Your best solution will be in creating a view (or computed column in that table) that will create a date column from that varchar date and use it for dates comparison.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 5

thanks.

and another question:

1- how can I convert ddMMyy to yyyyMMdd?

2 - I have 7 tables that they have this tpe of date column. is there any way to Update these columns from ddMMyy to yyyyMMdd format?

Answer 6

Try

declare @t table (cdt varchar(6))
insert into @t values ('311210'),('170999')

select cdt, case when ISDATE(F.dt) = 1 then cast(F.dt as DATE) end as [dt]

from @t 
CROSS APPLY (SELECT case when right(cdt,2) LIKE '[0-9][0-9]' 
then case when cast(right(cdt,2) as smallint) <50 then '20' else '19' end 
else null end + right(cdt,2) + substring(cdt,3,2) + left(cdt,2) as dt) F

So, I would create a new date column in these tables, update the value in them using the character value using the formula I posted.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 7

try this, little bit modified from the earlier post

declare @t table (cdt varchar(6))
insert into @t values ('311210'),('170999'),('999999')
select cdt, convert(datetime,case when isdate(right(cdt,2)+ SUBSTRING(cdt,3,2)+left(cdt,2))=1
 then right(cdt,2)+ SUBSTRING(cdt,3,2)+left(cdt,2) else NULL end)
from @t

Regards
Satheesh

Answer 8

thank you so much for these solution.

I have an another solution too, i wrote this code :

select * from [MyTable] where REPLACE(convert(date,Stuff(Stuff(dateColumn,5,0,'.'),3,0,'.'), 4), '-', SPACE(0));

and it convert ddMMyy to yyyyMMdd successfuly.

I'm trying to write a store procedure with some parameters like TableName and Column Name.

IF OBJECT_ID ( 'sp_Test', 'P' ) IS NOT NULL 
    DROP PROCEDURE sp_Test;
GO

CREATE PROCEDURE sp_Test
    @TableName nvarchar(50), 
    
AS  

DECLARE @cols AS VARCHAR(MAX), 
        @cmd AS VARCHAR(MAX),
        @P AS VARCHAR(MAX);
       
SELECT @P = 'SELECT [MyDateColumn] FROM ' + @TableName;
SELECT @cols = REPLACE(convert(date,Stuff(Stuff(@P,5,0,'.'),3,0,'.'), 4), '-', SPACE(0));
SET @cmd = 'SELECT * FROM ' + @TableName + ' WHERE ' + @cols + ' <=20090609';
execute(@cmd)

but it dose not work. i do not know what is my mistake?

Answer 9

You are making it overly complicated. You had seven tables, right? Write one procedure per table, and don't endulge in complex dynamic SQL.

I don't understand what you intend to achieve with your code.

SELECT @P = 'SELECT [MyDateColumn] FROM ' + @TableName;

So @P now reads:

   SELECT [MyDateColumn] FROM SomeTableName

Then you try:

   SELECT @cols = REPLACE(convert(date,Stuff(Stuff(@P,5,0,'.'),3,0,'.'), 4), '-', SPACE(0));

You are doing some manipulation with that query text and then try convert it to a date despite that there is nothing looking like a date in that query text. You also seem to be looking for period, which also is nowhere to be seen, unless there is one in the table name.

Your attempt to use the replace function on the date data type indicates that you don't understand how date/time data types works: they are not strings, they do not have a format. Instead they have an internal binary represenation.

You should to something like:

SET XACT_ABORT ON
BEGIN TRANSACTION

ALTER TABLE tbl ADD newgoodcol date

UPDATE tbl
SET        newgoodcol = convert(date,
             substring(oldbadcol, 5, 2) +
             substring(oldbadcol, 3, 2) +
             substring(oldbadcol, 1, 2))

-- Only if the old column is NOT NULL
ALTER TABLE tbl ALTER COLUMN newgoodcol date NOT NULL

ALTER TABLE tbl DROP oldbadcol

EXEC sp_rename 'tbl.newgoodcol', 'tbl.oldbadcol', 'COLUMN'

COMMIT TRANSACTION

This should work in theory. In practice it will fail, since I can almost guarantee that you have bad data in the tables. This is why I have wrapped the operation in a transaction. You will need to decide what to do with the bad data. Something like 310606 is fairly easy deal with. Others will be more problematic.

And, oh, you will need to review whether the two-digit years are handled correctly. SQL Server makes a "guess" about the century.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 10

Try

SELECT *  FROM [MyTable] WHERE cast([s_Date] as datetime)  >=  T1  AND cast([s_Date] as datetime)  <=  T2 ;

---

Many Thanks & Best Regards, Hua Min

Answer 11

thank you for you reply. it works perfectly on SQL Server (query document)

but when i try to run this query from C# :

SELECT * FROM [TableName] WHERE cast([s_Date] as date) >= '2012-11-07' AND  cast([s_Date] as date) <= '2012-12-26'

this commad not work an return 0 row!

but this command work ok :

SELECT * FROM [TableName] WHERE cast([s_Date] as date) >= '2012-11-07';

and i have 108 recored between these days : 2012-11-07 to 2012-12-26

I my so confiused! my code like this :

string T1 = date1.ToString("yyyy-MM-dd");
string T2 = date2.ToString("yyyy-MM-dd");
SqlCommand cmd = new SqlCommand();
cmd.Connection = _sqlCn;
cmd.CommandText = "SELECT * FROM [" + TableName+ "] WHERE cast([s_Date] as date) >= '" + T1 +
 "' AND  cast([s_Date] as date) <= '"+ T2 +"'";
cmd.CommandType = CommandType.Text;

SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);

what should i do?

Answer 12

You need to pass your dates as parameters, e.g.

SqlCommand cmd = new SqlCommand();
cmd.Connection = _sqlCn;
cmd.CommandText = "SELECT * FROM [" + TableName+ @"] WHERE cast([s_Date] as date) >= @StartDate 
AND  cast([s_Date] as date) <= @EndDate";
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(New SqlParameter("@StartDate",date1));
cmd.Parameters.Add(New SqlParameter("@EndDate",date2));
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);

You may need to adjust the cmd.Parameters.Add lines - there are plenty of samples you can google.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 13

cmd.Parameters.Add(New SqlParameters("@StartDate",date1));
cmd.Parameters.Add(New SqlParameters("@EndDate",date2));

There are several ways to skin this cat. I believe that this constructor for SqlParameter (without s) is depracted. I usually write it as:

cmd.Paramerters.Add("@StartDate", SqlDbType.Date);
cmd.Parameters{"@StartDate"].Value = date1;

I cannot be enough emphasisd that the code that Izadimehr is completely unacceptable in a professional context. That is, if you write code for some hobby project, it may pass, but if someone is paying you to write the code, it is not. Mastering parameterised statement is an absolute must if you want to write data-access code. There are several reasons, and one is that you don't have bother about date format when passing dates to SQL Server - the conversion happens on client level.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 14

thanks for reply.

I trying this code :

cmd.CommandText = "SELECT * FROM [" + TableName + @"] WHERE cast([s_Date] as date) >= @SDate";
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new  SqlParameter("@SDate",T1));

it is ok.

but this :

cmd.CommandText = "SELECT * FROM [" + TableName + @"] WHERE cast([s_Date] as date) >= @SDate AND cast([s_Date] as date) <= @EDate";
                  
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new  SqlParameter("@SDate",T2));
cmd.Parameters.Add(new SqlParameter("@EDate",T2));

it does not work! and return 0 row!

why?! i'm so confiused!

and Erland Sommarskog said :

I cannot be enough emphasisd that the code that Izadimehr is completely unacceptable in a professional context. That is, if you write code for some hobby project, it may pass, but if someone is paying you to write the code, it is not. Mastering parameterised statement is an absolute must if you want to write data-access code. There are several reasons, and one is that you don't have bother about date format when passing dates to SQL Server - the conversion happens on client level.

what should i do? change database table? change column format from ddMMyy to yyyyMMdd? or any suggestion.

thanks.

Answer 15

If you have not changed gps_Date from ddMMyy, then the expression

   cast(gps_Date AS date)

will of course give you rubbish. Say that the actual date is 15 Dec 2012, which is stored as "151212". When you cast this to date you get 12 Dec 2015, and then it goes downhill from there.

And, yes, you should change the column. Not to yyyyMMdd, but to the date data type! You should not store dates as strings as all. You should store them with the date data type nothing else. As said previous, this is not a string type, but it uses some internal binary representation that you should bother about.

Please see my previous post for how to change your table.

Obviously, you must also alter the code that stores the data, and, yes it will be some work. But what you have now is a complete headache to work with, and with no potential for good performance. You will have to swallow that bitter pill at some point anyway. The earlier you do, the less the impact and the cost.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 16

In your code above you pass the same value for both parameters.

cmd.Parameters.Add(new  SqlParameter("@SDate",T2));
cmd.Parameters.Add(new SqlParameter("@EDate",T2));

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 17

oh! this is my mistake on typing here!

my code is ok!

cmd.Parameters.Add(new  SqlParameter("@SDate",T1));
cmd.Parameters.Add(new SqlParameter("@EDate",T2));

but it does not work!