This blog post tries to provide few of very common scenarios and its solutions that developers would come across in development phase.
Standard Date CONVERT formats
Declare @Date Datetime = GETDATE()
Select @Date
SELECT DATEADD(month, DATEDIFF(month, '', @date), '');
Select CONVERT(Varchar(25),@date,100) as 'Converted Value', 'Mon DD YYYY HH:MIAM (or PM)' as 'Converted Format' union all
Select CONVERT(Varchar(25),@date,1),'MM/DD/YY' union all
Select CONVERT(Varchar(25),@date,101),'MM/DD/YYYY' union all
Select CONVERT(Varchar(25),@date,2),'YY.MM.DD' union all
Select CONVERT(Varchar(25),@date,102),'YYYY.MM.DD' union all
Select CONVERT(Varchar(25),@date,3),'DD/MM/YY' union all
Select CONVERT(Varchar(25),@date,103),'DD/MM/YYYY' union all
Select CONVERT(Varchar(25),@date,4),'DD.MM.YY' union all
Select CONVERT(Varchar(25),@date,104),'DD.MM.YYYY' union all
Select CONVERT(Varchar(25),@date,5),'DD-MM-YY' union all
Select CONVERT(Varchar(25),@date,105),'DD-MM-YYYY' union all
Select CONVERT(Varchar(25),@date,6),'DD Mon YY' union all
Select CONVERT(Varchar(25),@date,106),'DD Mon YYYY' union all
Select CONVERT(Varchar(25),@date,7),'Mon DD, YY' union all
Select CONVERT(Varchar(25),@date,107),'Mon DD, YYYY' union all
Select CONVERT(Varchar(25),@date,108),'HH:MM:SS' union all
Select CONVERT(Varchar(25),@date,109),'Mon DD YYYY HH:MI:SS:MMMAM (or PM)' union all
Select CONVERT(Varchar(25),@date,10),'MM-DD-YY' union all
Select CONVERT(Varchar(25),@date,110),'MM-DD-YYYY' union all
Select CONVERT(Varchar(25),@date,11),'YY/MM/DD' union all
Select CONVERT(Varchar(25),@date,111),'YYYY/MM/DD' union all
Select CONVERT(Varchar(25),@date,12),'YYMMDD' union all
Select CONVERT(Varchar(25),@date,112),'YYYYMMDD' union all
Select CONVERT(Varchar(25),@date,113),'DD Mon YYYY HH:MM:SS:MMM(24h)' union all
Select CONVERT(Varchar(25),@date,114),'HH:MI:SS:MMM(24H)' union all
Select CONVERT(Varchar(25),@date,120),'YYYY-MM-DD HH:MI:SS(24h)' union all
Select CONVERT(Varchar(25),@date,121),'YYYY-MM-DD HH:MI:SS.MMM(24h)' union all
Select CONVERT(Varchar(25),@date,126),'YYYY-MM-DDTHH:MM:SS:MMM' union all
Select CONVERT(nVarchar(25),@date,130),'DD Mon YYYY HH:MI:SS:MMMAM' union all
Select CONVERT(Varchar(25),@date,131),'DD/MM/YYYY HH:MI:SS:MMMAM'
How to separate date from a string
Create table testing(id int identity(1,1), enddate varchar(40))
insert into testing values('End Date: 04/30/2021')
insert into testing values('06/30/2027')
insert into testing values('something something something 06/30/2027')
insert into testing values('13.a')
insert into testing values('Section 23')
insert into testing values('End Date: 06/30/2027')
insert into testing values('End Date: 12/31/2016 a as asdadasd')
;With cte as
(
select ID, enddate,
SUBSTRING(enddate, patindex('%[0-9][0-9]/[0-9][0-9]/[0-9][0-9]%', enddate), 10) AS Actualdate
FROM testing
)
select id,enddate,
case when Isdate(right(Actualdate,10)) = 1 then Actualdate
else null end Actualdate from cte
Drop table testing
How to calculate age from a date
create table students(student_id int,DOB date)
Insert into students Values(1,'19790111'),(2,'19790219'),(3,'20200408')
Select student_id,DOB,Cast(getdate() as date),
cast((DATEDIFF(m, DOB, GETDATE())/12) as varchar) as years,
cast((DATEDIFF(m, DOB, GETDATE())%12) as varchar) as ' Months',
cast((DATEDIFF(d, DOB, GETDATE())%365) as varchar) as ' Days'
From students
Drop table students
If you enjoyed this blog post, feel free to share it with your friends!
SQLZealots 