There are many formats supported by SQL Server - see the MSDN Books Online on CAST and CONVERT. Most of those formats are dependent on what settings you have - therefore, these settings might work some times - and sometimes not.

The way to solve this is to use the (slightly adapted) ISO-8601 date format that is supported by SQL Server - this format works always - regardless of your SQL Server language and dateformat settings.

The ISO-8601 format is supported by SQL Server comes in two flavors:

• YYYYMMDD for just dates (no time portion); note here: no dashes!, that's very important! YYYY-MM-DD is NOT independent of the dateformat settings in your SQL Server and will NOT work in all situations!

or:

• YYYY-MM-DDTHH:mm:ss for dates and times - note here: this format has dashes (but they can be omitted), and a fixed T as delimiter between the date and time portion of your DATETIME.

This is valid for SQL Server 2000 and newer.

So in your specific case - use these strings:

insert into table1 values('2012-02-21T18:10:00', '2012-01-01T00:00:00');

and you should be fine (note: you need to use the international 24-hour format rather than 12-hour AM/PM format for this).

Alternatively: if you're on SQL Server 2008 or newer, you could also use the DATETIME2 datatype (instead of plain DATETIME) and your current INSERT would just work without any problems! :-) DATETIME2 is a lot better and a lot less picky on conversions - and it's the recommend date/time data types for SQL Server 2008 or newer anyway.

SELECT
   CAST('02-21-2012 6:10:00 PM' AS DATETIME2),     -- works just fine
   CAST('01-01-2012 12:00:00 AM' AS DATETIME2)   -- works just fine  

Don't ask me why this whole topic is so tricky and somewhat confusing - that's just the way it is. But with the YYYYMMDD format, you should be fine for any version of SQL Server and for any language and dateformat setting in your SQL Server.

You are storing dates as strings - why? Opening_Date and Date should be date or datetime, not varchar. But before you can fix that, you need to identify the rows that are causing the conversion problem:

SELECT cod_store, [Date]
FROM dbo.FactTransactions
WHERE ISDATE([Date]) = 0;

SELECT cod_storekey, Opening_Date
FROM dbo.DimStore
WHERE ISDATE(Opening_Date) = 0;

And now that you've fixed the question and I know you're using 2012 and not 2008 R2, it might be cleaner to use TRY_CONVERT(), especially since it will allow you to identify any rows where the month and day are transposed incorrectly. For example, assuming you expect dates to be stored as mm/dd/yyyy strings:

SELECT cod_store, [Date]
FROM dbo.FactTransactions
WHERE TRY_CONVERT(datetime, [Date], 101) = 0;

SELECT cod_storekey, Opening_Date
FROM dbo.DimStore
WHERE TRY_CONVERT(datetime, Opening_Date, 101) = 0;

In addition to identifying garbage rows where users have stored nonsense like floob and 9992-13-36 as "dates," this will also identify rows where users have stored 13/07/1999 instead of 07/13/1999 (but there is no way to know if 05/06/2000 is meant to be May 6 or June 5).

Now you need to fix those rows before you can correct the tables.

ALTER TABLE dbo.DimStore ALTER COLUMN Opening_Date date; -- or datetime;
ALTER TABLE dbo.FactTransactions ALTER COLUMN [Date] date; -- or datetime;

You might also consider renaming the Date column to be (a) less vague and (b) not a reserved word.

If you can't fix the tables, then you need to change your query:

UPDATE T1
SET ORDINAL = DATEDIFF(DAY, 
  CASE WHEN ISDATE(T2.Opening_Date) = 1 THEN T2.OpeningDate END,
  CASE WHEN ISDATE(T1.[Date]) = 1 THEN T1.Date END)
FROM dbo.FactTransactions AS T1
INNER JOIN dbo.DimStore AS T2 
  ON T1.cod_store = T2.cod_storeKey
WHERE ISDATE(T2.Opening_Date) = 1
  AND ISDATE(T1.[Date]) = 1;

And @RLF brought up a great point, too; if you can't fix the table, then the date columns could contain data that represent a specific date (say, September 7) but be entered in the wrong format (e.g. on a US English system, entered as a string in British format, 7/9/2015). So really, you need to fix the table and stop storing these things as strings.

Some other useful material:

• Bad habits to kick : mis-handling date / range queries
• Bad habits to kick : avoiding the schema prefix

Try the following:

CREATE OR ALTER PROCEDURE [dbo].[urunGiris]
	@TableName VARCHAR(100)
	, @MalzemeStokNo VARCHAR(50)
	, @MalzemeAd VARCHAR(100)
	, @Irsaliye VARCHAR(100)
	, @MalzemeAgirlik VARCHAR(50)
	, @GirenMiktar INT
	, @GirenTonaj FLOAT(53)
	, @CikanMiktar INT
	, @CikanTonaj FLOAT(53)
	, @KalanMiktar INT
	, @KalanTonaj FLOAT(53)
	, @Tarih DATETIME
AS
	BEGIN
		DECLARE @SQL NVARCHAR(MAX);
		SET @SQL = N'insert into ' + @TableName
				   + N'(MalzemeStokNo, MalzemeAd, Irsaliye, MalzemeAgirlik,GirenMiktar,GirenTonaj,CikanMiktar,
CikanTonaj,KalanMiktar,KalanTonaj,Tarih) 
	values (@MalzemeStokNo1,
@MalzemeAd1,
@Irsaliye1,
@MalzemeAgirlik1,
@GirenMiktar1,
@GirenTonaj1,
@CikanMiktar1,
@CikanTonaj1,
@KalanMiktar1,
@KalanTonaj1,
@Tarih1)';
		EXECUTE sp_executesql
			@SQL
			, N'@MalzemeStokNo1 varchar(50),@MalzemeAd1 varchar(100),@Irsaliye1 varchar(100),@MalzemeAgirlik1 varchar(50),
 @GirenMiktar1 int,@GirenTonaj1 FLOAT (53),@CikanMiktar1 int,@CikanTonaj1 FLOAT (53),@KalanMiktar1 int,@KalanTonaj1 FLOAT (53),@Tarih1 datetime'
			, @MalzemeStokNo1 = @MalzemeStokNo
			, @MalzemeAd1 = @MalzemeAd
			, @Irsaliye1 = @Irsaliye
			, @MalzemeAgirlik1 = @MalzemeAgirlik
			, @GirenMiktar1 = @GirenMiktar
			, @GirenTonaj1 = @GirenTonaj
			, @CikanMiktar1 = @CikanMiktar
			, @CikanTonaj1 = @CikanTonaj
			, @KalanMiktar1 = @KalanMiktar
			, @KalanTonaj1 = @KalanTonaj
			, @Tarih1 = @Tarih;
	END;

Try this:

SELECT 
    CASE 
        WHEN ISDATE(field_name)=1 THEN CONVERT(datetime, field_name, 103 ) 
        ELSE null 
    END
FROM your_table

I think you need to check that your date is correct and after that convert it. You have a problem with your data.

Another solution:

Instead of convert use try_convert function:

try_convert(date,field_name,101) AS ChangeDate