MS SQL – Date and Time Style Conversion Problem

castsql servertype conversion

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.

Best Answer

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.

Related Solutions

Sql-server – Conversion failed when converting the varchar value ‘Hours’ to data type int

This is not clear what output is needed. You are trying to convert text strings to int. This obviously does not work. Besides this type of formatting might fit better outside of SQL Server.

If you want something like 5 Hours 15 Minutes you can try this query:

[Time Spent(In hrs)] =
    CAST(
        SUM(
            DATEDIFF(hour, ld_time_tracker_start_time, ld_time_tracker_end_time)                     
        )
    as varchar(10))
    +' Hours '+ 
    CAST(
        SUM(
            DATEDIFF(minute, ld_time_tracker_start_time, ld_time_tracker_end_time) % 60       
        )
    as varchar(10))
+' Minutes'

Note that I have replaced HH by hour. This is easier to read and understand and it avoid mistakes. See DATEDIFF (Transact-SQL).

SQL Server – How to Convert VARCHAR to DATETIME Format

There is too much precision in the varchar to be converted into datetime.

One option(better in my opinion) would be to change the target column to datetime2(7). Then you can convert like this:

declare @dt varchar(50)
set @dt = '2015-12-02 20:40:37.8130000'

select cast(@dt as datetime2(7));

If changing the column is not an option the you can do the conversion like this:

declare @dt varchar(50)
set @dt = '2015-12-02 20:40:37.8130000'

select cast(cast(@dt as datetime2(7))as datetime)

Best Answer

Related Solutions

Sql-server – Conversion failed when converting the varchar value ‘Hours’ to data type int

SQL Server – How to Convert VARCHAR to DATETIME Format

Related Question