none
How to make SQL server "Case Statement" case sensitive in converting MySQL STR_TO_DATE function into a UDF

    Pregunta

  • /*
    Usage
    select dbo.STR_TO_DATE('20121105','%Y%m%d')
    select dbo.STR_TO_DATE('2012-11','%Y-%m')
    select dbo.STR_TO_DATE('05:06:07','%H:%i:%s')
    select dbo.STR_TO_DATE('05','%m')
    select dbo.STR_TO_DATE('Jun','%m')
    select dbo.STR_TO_DATE('June','%M')
    select dbo.STR_TO_DATE('90','%y')
    select dbo.STR_TO_DATE('1990','%Y')
    
    
    */
    
    CREATE function STR_TO_DATE(@dateValue varchar(50), @MySQLStyleformat varchar(200))
    returns datetime
    as
    begin
    declare @dt datetime;
    
    if @dateValue is not null
    select @dt= 
    case rtrim(ltrim(@MySQLStyleformat)) on collate Latin1_General_CS_AI
    when '%Y%m%d' then convert(datetime,@dateValue,120)
    when '%Y-%m-%d' then convert(datetime,@dateValue,120)
    when '%m%d%Y' then convert(datetime,@dateValue,120)
    when '%Y-%m' then convert(datetime,@dateValue + '-01',120)
    when '%Y-%m-%d' then convert(datetime,@dateValue ,120)
    when '%Y%m%d %H:%i:%s' /*yyyyMMdd H:mm:ss*/ then convert(datetime,@dateValue ,120)
    when '%y%m%d %H:%i:%s' /*yyMMdd H:mm:ss*/ then convert(datetime,@dateValue ,120)
    when '%H:%i:%s' /*H:mm:ss*/ then convert(datetime,@dateValue ,120)
    when '%H:%i' /*H:mm*/ then convert(datetime,@dateValue ,120) 
    when '%m' /*MM*/ then convert(datetime,@dateValue + '01 1900' ,120) 
    when '%M' /*MMMM*/ then convert(datetime,@dateValue + '01 1900' ,120)  
    when '%b' /*MMM*/ then convert(datetime,@dateValue + '01 1900',120)  
    when '%y' /*yy*/ then convert(datetime,substring(cast(datepart(yy, getdate()) as varchar(4)),1,2) + @dateValue + '/1/1' ,120)  
    when '%Y' /*yyyy*/ then convert(datetime,@dateValue + '1/1' ,120)  
    when '%m%d' /*MMDD*/ then convert(datetime,@dateValue + '1900' ,120)  
    when '%H:%i ' /*h:mm tt*/ then convert(datetime,@dateValue ,120)  
    end

    The input for '%y' will be two digit and for '%Y' of four, if we can make case statement to differentiate between y and Y i.e. upper and lowercase then  our STR_TO_DATE will work as desired. 

    Now this is throwing exception when we execute 

    select dbo.STR_TO_DATE('1990','%Y')

    as the statement when '%y' /*yy*/ then convert(datetime,substring(cast(datepart(yy, getdate()) as varchar(4)),1,2) + @dateValue + '/1/1' ,120)  executed first and date became '2019090/1/1' 

    We do not need case sensitiveness for whole DB, therefor we can not switch to Latin1_general_CI_AS. 

    All suggestions are welcome.


     
    miércoles, 26 de diciembre de 2012 8:04

Respuestas

  • Hi Allen Li - MSFT

    Thanks for the reply.

    Actually the MySql styled format (%y and %Y) is comes from a table which is maintained through a UI, saving %Y as %Y2 will force us to change the UI code. Therefor we have used ASCII function to identify between y and Y and make one statement for %Y and %y. This is working fine

    set @MySQLStyleformat=replace(@MySQLStyleformat,'%','~');
    
    if @dateValue is not null
    SET @dt=(select  
    case rtrim(ltrim(@MySQLStyleformat)) 
    when '~y' then 
    
    case when ascii(replace(@MySQLStyleformat,'~',''))= ascii('y') then y
    else Y end


    • Marcado como respuesta akfi jueves, 27 de diciembre de 2012 9:08
    jueves, 27 de diciembre de 2012 9:07

Todas las respuestas

  • Hi akfkmupiwu,

    When executing

    select dbo.STR_TO_DATE('1990','%Y')

    Inside STR_TO_DATE function, it will execute:

    when '%y' /*yy*/ then convert(datetime,substring(cast(datepart(yy, getdate()) as varchar(4)),1,2) + @dateValue + '/1/1' ,120)  

    To distinguish them, you can modify '%Y' as '%Y2', I made a simple example,

    CREATE function STR_TO_DATE(@dateValue varchar(50), @MySQLStyleformat varchar(200))
    returns varchar(200)
    as
    begin
    declare @dt varchar(200);
    
    if @dateValue is not null
    select @dt= 
    case rtrim(ltrim(@MySQLStyleformat))  
    when '%y' /*yy*/ then '%y'  
    when '%Y2' /*yyyy*/ then 'Y'   
    end
    return @dt
    
    end
    
    select dbo.STR_TO_DATE('1990','%Y2')
    --Y
    
    drop function STR_TO_DATE
    

    There are some documents talk about the same topic, you can refer to them too:

    http://blogs.msdn.com/b/ssma/archive/2011/06/15/converting-mysql-str-to-date-function.aspx
    http://www.sqlusa.com/bestpractices/datetimeconversion/



    Allen Li
    TechNet Community Support

    jueves, 27 de diciembre de 2012 6:37
    Moderador
  • Hi Allen Li - MSFT

    Thanks for the reply.

    Actually the MySql styled format (%y and %Y) is comes from a table which is maintained through a UI, saving %Y as %Y2 will force us to change the UI code. Therefor we have used ASCII function to identify between y and Y and make one statement for %Y and %y. This is working fine

    set @MySQLStyleformat=replace(@MySQLStyleformat,'%','~');
    
    if @dateValue is not null
    SET @dt=(select  
    case rtrim(ltrim(@MySQLStyleformat)) 
    when '~y' then 
    
    case when ascii(replace(@MySQLStyleformat,'~',''))= ascii('y') then y
    else Y end


    • Marcado como respuesta akfi jueves, 27 de diciembre de 2012 9:08
    jueves, 27 de diciembre de 2012 9:07