Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
NickC
Yak Posting Veteran
68 Posts |
 Posted - 2011-10-18 : 09:11:48
|
| HiI'm trying to write a piece of code which will look at a varchar field and select the left 10 characters to produce a date and convert to date - that part is finehowever some dates have been misentered so I want something to exclude them and flag them up underneath as a seperate query?is this possible |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
|
|
NickC
Yak Posting Veteran
68 Posts |
Posted - 2011-10-18 : 09:26:33
|
| I tried, but seems to only validate it after the conversation so is erroring before I ahve chance :( |
 |
|
|
NickC
Yak Posting Veteran
68 Posts |
Posted - 2011-10-18 : 09:31:18
|
| Okay close to it, im doing isdate as jsut left(columnname,10) but its saying some arent dates cos its in dd/mm/yyyy format and its looking for mm/dd/yyyy instead? |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-10-18 : 09:33:06
|
quote:
Originally posted by NickC
I tried, but seems to only validate it after the conversation so is erroring before I ahve chance :(
case when isdate(left(col, 10)) = 1 then convert(datetime, left(col, 10)) else null end
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
NickC
Yak Posting Veteran
68 Posts |
Posted - 2011-10-18 : 09:36:24
|
| Hero! I just got there used SET DATEFORMAT dmy;GObefore writing it, and then used your bit! Thanks!! |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-10-18 : 09:39:57
|
yes. ISDATE() is depending on these settings. As stated in the link that i posted earlierquote:
The return value of ISDATE depends on the settings set by SET DATEFORMAT, SET LANGUAGE and default language option
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|
|
varchar and datetime issue