Convert and Substring

Question

Hello

I am trying to convert the birth of date into the listed format

mm-dd-yyyy

I am using convert( varchar birth_date, 110) and it works but I have a hard time to extract the month & date to create a string

I need the month & day like 0925

Please help!!!

Answer 1

declare @birth_date date = '20200912'  
  
select   
	@birth_date  
	, format(month(FORMAT( @birth_date, 'MM/dd/yyyy')), '00') + format(day(FORMAT( @birth_date, 'MM/dd/yyyy')), '00')  


  

Answer 2

Here is another solution:

declare @birth_date date = '20200912'  
SELECT right(convert(char(8), @birth_date, 112), 4)  

A disadvantage with the format function is that is implemented in the CLR and the context switch incurs on overhead which can be costly when there many rows.

Answer 3

declare @bdate date = '20000925'

Select Convert(varchar(10),@bdate,101) --mm/dd/yyyy
, Convert(varchar(10),@bdate,110) --mm-dd-yyyy


,format(@bdate,'MM-dd-yyyy') --mm-dd-yyyy

,format(@bdate,'MMdd') --MMdd
, Right(Convert(varchar(8),@bdate,112),4) --MMdd
,Concat(Format(Month(@bdate),'00'),Format(Day(@bdate),'00')) --MMdd
,Right('00'+Cast(Month(@bdate) as varchar(2)),2)+Right('00'+Cast(Day(@bdate) as varchar(2)),2)  --MMdd

Answer 4

Therefore, if FORMAT is less performing we can minimize its invocation.
Thinking about it better, it can be done!

declare @birth_date date = '20200912'

 select 
     @birth_date
     , format(@birth_date, 'MMdd')

Thanks to Erland for the food for thought.

Answer 5

Hi @Ysa8989 ，

You can use format function if you need a specific date format
(FORMAT (Transact-SQL)):

    declare @birth_date date = '20200925'  
    select convert( varchar(15), @birth_date, 110) birthdate,format(@birth_date,'MMdd')  

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Best Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.