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

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

Get month on date varchar

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Get month on date varchar

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us