Casting of dates stored as Varchar

Navigation
0

Good, everyone again requesting your support as I have a question about Casting a date, which is stored in the DB as Varchar with the following format 20161105 0207 , I need to make a cast of this DateTime , the first 8 digits correspond to the date and the last four times per minute , so you can generate a query that shows records in a range of dates em> , I tried the options that I show in the lower part but they do not give me the result, if someone knows how to do it I will be infinitely grateful. 

select CAST(cor_fecha_transaccion as datetime) from Corresponsalias

select CONVERT(datetime,cor_fecha_transaccion,20)from Corresponsalias
    
asked by Alberto Arenas 13.12.2016 в 02:35
source

3 answers

1

Sadly, for the version of SQL Server that you use, I think you have no choice but to manipulate the string using a series of substring s to transform it into a format recognized by the database so that you can then cast it to a datetime .

The simplest thing would be to change the string 20161105 0207 to 2016-11-05 02:07:00 , so you can make a cast(... as datetime) : 

declare @dtstring as varchar(14) = '20161105 0207';
select cast(substring(@dtstring,1,4) + '-' + 
            substring(@dtstring,5,2) + '-' +
            substring(@dtstring,7,2) + ' ' +
            substring(@dtstring,10,2) + ':' +
            substring(@dtstring,12,2) + ':00'
            as datetime)

Live demo .

Although I prefer to use cast , you can use the same technique with convert : 

declare @dtstring as varchar(14) = '20161105 0207';
select convert(datetime,
               substring(@dtstring,1,4) + '-' + 
               substring(@dtstring,5,2) + '-' +
               substring(@dtstring,7,2) + ' ' +
               substring(@dtstring,10,2) + ':' +
               substring(@dtstring,12,2) + ':00',
               120)

Live demo .

Although these options solve the problem with your query, please seriously consider converting the field type in your varchar to datetime table. It is not correct to use varchar to save a date. Not only does this cause you such problems, but it also prevents you from using indexes in these columns to improve performance.

    
answered by 13.12.2016 / 02:56
source
0

To transform that date into a datetime, it would be something like this 

SELECT convert(datetime, STUFF(STUFF('31012016',3,0,'-'),6,0,'-'), 105)

I find it dubious to work on incorrect data types, when the database engine gives you the data that corresponds, so you can make future queries that you want to do.

    
answered by 13.12.2016 в 02:48
0

You could use STUFF in this way, to add the two points of the time, in addition to adding the SET DATEFORMAT ymd : 

SET DATEFORMAT ymd
SELECT CONVERT(DATETIME,STUFF('20161105 0207',12,0,':'))
    
answered by 19.12.2016 в 19:06
Open a file from another directory with C ++ in GNU / Linux Spring 4 - Apache POI - Security: Error 403 when generating Excel spreadsheet