Get month on date varchar

Question

1.00/5 (1 vote)

See more:

How do I get the month on date which the datatype of the date field is varchar? Help guys.

I've tried a query below, but it's not working:

Error:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

What I have tried:

select datepart(mm, cast('March-04-2008' as datetime))

Posted 18-Mar-18 1:21am

John Th

Updated 18-Mar-18 3:28am

Add a Solution

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)

Kornfeld Eliyahu Peter · Answer 1 · 2018-03-18T01:43:00

Solution 1

SQL's CAST can only convert string-to-date if it is in ISO8601 format - 'yy:MM:ddTHH:mm:ss.nnn'...
The CONVERT method can deal with different formats, but not with the format you supplied here...
CAST and CONVERT (Transact-SQL) | Microsoft Docs[^]

Posted 18-Mar-18 1:43am

Kornfeld Eliyahu Peter

Comments

Maciej Los 18-Mar-18 9:12am

5ed!

Kornfeld Eliyahu Peter 18-Mar-18 9:14am

Thank you!

Maciej Los · Answer 2 · 2018-03-18T03:29:00

Solution 2

In addition to solution #1 by Kornfeld Eliyahu Peter[^]...

If a month is always at the beginning of string-date, i'd suggest to create helper-table. See:

SQL

DECLARE @tmp TABLE(Mnth INT IDENTITY(1,1), MnthName VARCHAR(30))
INSERT INTO @tmp (MnthName)
VALUES('January'), ('February'), ('March') --and so on...

DECLARE @stringDates TABLE(stringDate VARCHAR(30))
 INSERT INTO @stringDates (stringDate)
 VALUES('March-04-2008'), ('February-28-2008'), ('January-14-2008')

SELECT s.stringDate AS OriginalDate, m.Mnth
FROM @tmp AS m INNER JOIN @stringDates AS s ON m.MnthName = LEFT(stringDate, CHARINDEX('-',stringDate)-1)

Final note: Never store dates as a string! Date is a date and nothing else!

Posted 18-Mar-18 3:29am

Maciej Los

Updated 18-Mar-18 3:30am

v2

Comments

John Th 19-Mar-18 2:21am

Then what datatype should I use?

Maciej Los 19-Mar-18 3:18am

DateTime!

Richard Deeming 19-Mar-18 12:28pm

Actually, since there's no time part involved, date would be the better choice.

If there was a time involved, datetime2 would be better than datetime - it has a larger range, a larger default fractional precision, and optional user-specified precision.

John Th 19-Mar-18 3:05am

I want to save the date as; March-04-2018. What datatype should I use? Help me, please.

Maciej Los 19-Mar-18 3:19am

Wrong! Use date, not its string representation!
Converting between data types is bothersome and may cause loss of efficiency.

John Th 19-Mar-18 3:24am

Okay. Then how can I get the value of month in datetime?

Maciej Los 19-Mar-18 3:26am

Month(ProperDateTime) returns 3 for March
If you would like to display March from DateTime, use DateName function
;)

John Th 19-Mar-18 3:31am

I want to get the month number. For example: March, the return value is 03.

Maciej Los 19-Mar-18 3:49am

DECLARE @dt DATETIME = GETDATE()
SELECT RIGHT('00' + CONVERT(VARCHAR(2), DATEPART(MM, @dt)),  2) AS MyMonth 
--or
SELECT RIGHT('00' + CONVERT(VARCHAR(2), MONTH(@dt)),  2) AS MyMonth

John Th 19-Mar-18 3:47am

How? @losmac

John Th 19-Mar-18 4:25am

How to fix this?

SELECT stock_id, stock_qty, RIGHT('00' + CONVERT(VARCHAR(2), MONTH(stock_date)), 2) FROM tblstocks WHERE stock_date = '03';

Maciej Los 19-Mar-18 5:06am

Fix what?
Note: i can't see your screen and haven't an access to your HDD. And i don't even can read in your mind...

John Th 19-Mar-18 5:12am

I fixed it. https://ibb.co/dmpHEc

John Th 19-Mar-18 5:14am

Is it possible to convert it to MMMM-dd-yyyy? March-19-2018 will be return.

try6 — imgbb.com[^]