Author |
Topic |
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2014-03-18 : 12:38:03
|
Hi all I have the following Column Name : [Converted Date]Data Type : varchar(50)When I try and do month around the [Converted Date] I get the following error message “Msg 241, Level 16, State 1, Line 2Conversion failed when converting date and/or time from character string.”My Query isSELECT month([Created Date]) FROM [FDMS_PartnerReporting].[Staging].[Salesforce_MarketingReporting]Looking forward to your help RegardsD |
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-03-18 : 12:55:30
|
Sounds like you've got data in that column that can't be converted to date/time. Let's what these show:SELECT CAST([Created Date] AS datetime)FROM [FDMS_PartnerReporting].[Staging].[Salesforce_MarketingReporting]SELECT [Created Date]FROM [FDMS_PartnerReporting].[Staging].[Salesforce_MarketingReporting]WHERE ISDATE([Created Date]) = 0Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2014-03-18 : 12:57:17
|
If you can, you really should fix the data type. If you are storing a date value you should be using a datatype like DATE, DATETIME, SMALLDATETIME or DATETIME2.As for the particular error you are getting.. Since you didn't supply any sample data, I can only guess that you have bad data. But, if the data is fine then you might need to "tell" SQL what format it is in by first CONVERTing the string to an actual date value specifying the format. |
|
|
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2014-03-18 : 13:02:51
|
HI guys I had a look at the date format and its displayed as follows 17/03/201417/03/201418/03/2014eg day,month,year |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-03-18 : 13:08:49
|
quote: Originally posted by masond HI guys I had a look at the date format and its displayed as follows 17/03/201417/03/201418/03/2014eg day,month,year
Run my queries.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2014-03-18 : 13:39:55
|
Hi tkizer This following statement provided me with all the datesSELECT [Created Date]FROM [FDMS_PartnerReporting].[Staging].[Salesforce_MarketingReporting]WHERE ISDATE([Created Date]) = 0what i am trying to do is find the weekno, month, year from the created date |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-03-18 : 13:42:38
|
Did the one with CONVERT return an error? Or did it return all the dates too? If it didn't return an error, then use this:SELECT MONTH(CAST([Created Date] AS datetime))FROM [FDMS_PartnerReporting].[Staging].[Salesforce_MarketingReporting]Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2014-03-21 : 06:30:45
|
Always use proper datetime datatype to store date and time.Try thisSELECT month(convert(datetime,[Created Date],103)) FROM [FDMS_PartnerReporting].[Staging].[Salesforce_MarketingReporting]MadhivananFailing to plan is Planning to fail |
|
|
|