Click here to Skip to main content
14,332,142 members
Rate this:
Please Sign up or sign in to vote.
See more:
how can i use substring to select this NVARCHAR

1 is the month
4 and 5 days
2015 my year
PIEBALDconsult 29-Nov-15 12:45pm
Avoid string operations in SQL whenever possible. Please use Improve question to add context and detail so we can advise you better.
Sergey Alexandrovich Kryukov 29-Nov-15 12:53pm
Just don't store time-related data in the form of string, use proper data types.
Rate this:
Please Sign up or sign in to vote.

Solution 1

Using substring is the dirty way to do this. The problem is when your days and months become two digits; then you'd have to write a bunch of conditions to get the correct character or additionally use CHARINDEX to get the location of the / and then do something with the characters after it.

My suggestion would be to use CAST as a DateTime. Please see my example below:

SET language british
DECLARE @varMonth int, @varDay int, @varYear as int
SELECT @varDay = DAY(CAST('4/1/2015' AS datetime))
SELECT @varMonth = MONTH(CAST('4/1/2015' AS datetime))
SELECT @varYear = YEAR(CAST('4/1/2015' AS datetime))

SELECT @varDay AS Day, @varMonth AS Month, @varYear AS Year
Rate this:
Please Sign up or sign in to vote.

Solution 2

As suggested in the comments, it is not a good idea to use strings to store dates. There are just too many variants of how to represent a date in different parts of the world.

But if you are stuck with this and cannot change it, you can always convert the string to a date using CONVERT[^].

Once you have the data as a date[^] data type, it should be no problem to get the year, month and day. See SQL Date Functions[^]

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100