Getting specific string in SQL Server

Question

In SQL Server 2008, I want to get the year of following string format. Parameter will be one of following strings (DateTime.MinValue and DateTime.MaxValue from C#):

'1/1/0001 12:00:00 AM' and '12/31/9999 11:59:59 PM'

Here is my expected answer:

0001 from 1st string.
9999 from 2nd string.

try date_format function

Satya
– Satya

2013-05-02 04:03:24 +00:00
Commented May 2, 2013 at 4:03 — Satya
– Satya, Commented May 2, 2013 at 4:03

p.s.w.g · Accepted Answer · 2013-05-02 04:04:21Z

1

If you know what the format of the input string is, you can just do:

SELECT ... SUBSTRING([Input], PATINDEX('%[0-9][0-9][0-9][0-9]%', [Input]), 4)
FROM   ...

Or

SELECT ... SUBSTRING([Input], CHARINDEX('/', [Input], 4) + 1, 4)
FROM   ...

answered May 2, 2013 at 4:04

p.s.w.g

150k31 gold badges307 silver badges339 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Paras · Accepted Answer · 2013-05-02 04:20:52Z

0

This could be another way to do the same as well.

declare @test nvarchar(50)
SET @test='1/1/9999 12:00:00 AM'
declare @testdate date
set @testdate=CONVERT(date,@test,101)
select DatePart(year,@testdate)

Check out this sqlfiddle

answered May 2, 2013 at 4:20

Paras

3,1078 gold badges37 silver badges46 bronze badges

Collectives™ on Stack Overflow

Getting specific string in SQL Server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related