I need to convert Date and Time Style from USA format to German and than cast that into int data type. So for variable in case below this works fine if I want to display it in Standard 112 (ISO)

DECLARE @LoopDate datetime
SET @LoopDate = '12/23/2015'
SELECT @LoopDate AS Original, 
CAST(CONVERT(VARCHAR(8), @LoopDate, 112) AS int) AS int;

But when I put 104 which suppose to gives me error:

Conversion failed when converting the varchar value '23.10.2015' to data type int.

It looks like conversion is actually working but casting into int is not.


The 104 format has . in it and thats why it is failing vs the 112 has not dots so you can cast that as int. See https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017

DECLARE @LoopDate datetime
SET @LoopDate = '12/23/2015'
SELECT @LoopDate AS Original,
CONVERT(VARCHAR(8), @LoopDate, 104) as GermanDate_WithDot, -- see the dot  
 cast(replace(CONVERT(VARCHAR(8), @LoopDate, 104),'.','') as int)  as ProperlyCastedGermanDate_WithoutDot,
CAST(CONVERT(VARCHAR(8), @LoopDate, 112) AS int) AS ISOCasted_Date_asInt  ;

Note: Casting date to int for some later math is not a good idea.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.