Convert datetime to a varchar using a 2 digit year in SQL
Build intelligent, mission-critical applications using a scalable, hybrid database platform that has everything built in—from in-memory performance and advanced security to in-database analytics.
Scenario: You have datetime field and would like to convert it to a varchar based on sql server supplied format strings.
Sql server uses 2-digit year cut off on 2049. For example years 0-to-49 will be converted to 2000-2049, and years 50-to-99 will be converted to 1950-1999.
Question: How to convert datetime to varchar using the 2-digit year.
Let us create a sample table that will hold the datetime conversions,
1 create 2 table table_name ( 3 orig_date datetime, 4 conv_date varchar(100), 5 format_val int, 6 out_str varchar(100) 7 );
Here we will populate the table with sample data that will represent the conversion of datetime to varchar.
1 declare @curr_dt datetime 2 select @curr_dt = getdate() 3 4 INSERT INTO table_name (orig_date, conv_date, format_val, out_str) 5 VALUES (@curr_dt, convert(varchar(100), @curr_dt, 0), 0, 'mon dd yyyy hh:miAM (or PM)'), 6 (@curr_dt, convert(varchar(100), @curr_dt, 1), 1, 'mm/dd/yy'), 7 (@curr_dt, convert(varchar(100), @curr_dt, 2), 2, 'yy.mm.dd'), 8 (@curr_dt, convert(varchar(100), @curr_dt, 3), 3, 'dd/mm/yy'), 9 (@curr_dt, convert(varchar(100), @curr_dt, 4), 4, 'dd.mm.yy'), 10 (@curr_dt, convert(varchar(100), @curr_dt, 5), 5, 'dd-mm-yy'), 11 (@curr_dt, convert(varchar(100), @curr_dt, 6), 6, 'dd mon yy'), 12 (@curr_dt, convert(varchar(100), @curr_dt, 7), 7, 'Mon dd, yy'), 13 (@curr_dt, convert(varchar(100), @curr_dt, 8), 8, 'hh:mm:ss'), 14 (@curr_dt, convert(varchar(100), @curr_dt, 9), 9, 'mon dd yyyy hh:mi:ss:mmmAM (or PM)'), 15 (@curr_dt, convert(varchar(100), @curr_dt, 10), 10, 'mm-dd-yy'), 16 (@curr_dt, convert(varchar(100), @curr_dt, 11), 11, 'yy/mm/dd'), 17 (@curr_dt, convert(varchar(100), @curr_dt, 12), 12, 'yymmdd'), 18 (@curr_dt, convert(varchar(100), @curr_dt, 13), 13, 'dd mon yyyy hh:mm:ss:mmm(24h)'), 19 (@curr_dt, convert(varchar(100), @curr_dt, 14), 14, 'hh:mi:ss:mmm(24h)'), 20 (@curr_dt, convert(varchar(100), @curr_dt, 20), 20, 'yyyy-mm-dd hh:mi:ss(24h)'), 21 (@curr_dt, convert(varchar(100), @curr_dt, 21), 21, 'yyyy-mm-dd hh:mi:ss.mmm(24h)');
The resulting query will produce the following rows,
2 select * 3 from table_name;