substring in sql server ?

Question

1.00/5 (2 votes)

See more:

SQL

hi
how can i use substring to select this NVARCHAR
4-1-2015
5-1-2015

1 is the month
4 and 5 days
2015 my year

Posted 29-Nov-15 6:39am

sam9787

Add a Solution

Comments

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.
—SA

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

George Jonsson · Answer 1 · 2015-11-29T12:32:00

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[^]

jgakenhe · Answer 2 · 2015-11-29T12:06:00

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:

SQL

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

substring in sql server ?

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0