Click here to Skip to main content
16,016,180 members
Please Sign up or sign in to vote.
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
Updated 18-Mar-18 3:28am

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[^]
 
Share this answer
 
Comments
Maciej Los 18-Mar-18 9:12am    
5ed!
Kornfeld Eliyahu Peter 18-Mar-18 9:14am    
Thank you!
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!
 
Share this answer
 
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.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900