August 21, 2009 at 1:54 am
I'm using SQL Server 2005 Enterprise Edition and I have the following date formats in a varchar(50) field within a table:
02/01/1900
02/01/1900 00:00:00
02011900
02011900 00:00:00
1900/01/02
1900/01/02 00:00:00
19000102
19000102 00:00:00
1900-01-02
1900-01-02 00:00:00
I need to be able to check this field and check whether there are any entries before 01/01/1900. As the table is storing the date in a varchar format I'm having trouble carrying out the check. At this moment in time I'm not able to change the data type of the column.
Any thoughts/comments would be greatly appreciated.
Thanks in advance.
www.sqlAssociates.co.uk
August 21, 2009 at 2:09 am
Please see this link which will answer a lot of the issues you will have.
http://www.karaszi.com/SQLServer/info_datetime.asp
Feel free to post back after that with any other questions
August 21, 2009 at 2:40 am
Based on that dataset, using something like
SELECT DATEPART(YEAR,
CAST(
CASE ISDATE( a)
WHEN 1 THEN a
ELSE
RIGHT(REPLACE(a, ' 00:00:00', ''), 4) +
SUBSTRING(REPLACE(a, ' 00:00:00', ''), 3, 2) +
LEFT(a, 2)
END
AS DATETIME
)
)
FROM ---
replacing the a with the column name would work.
Having said that, it'd almost certainly break down with some of the more outlandish rubbish you're likely to have in there in all honesty. But it may give you a starting point
August 21, 2009 at 2:59 am
I created one table with following values:
create table dateformatting
(
Joiningdate VARCHAR(100)
)
insert into dateformatting values('2/1/1900')
insert into dateformatting values('2/1/1900 0:00')
insert into dateformatting values('2011900')
insert into dateformatting values('02011900 00:00:00')
insert into dateformatting values('1/2/1900')
insert into dateformatting values('1/2/1900 0:00')
insert into dateformatting values('19000102')
insert into dateformatting values('19000102 00:00:00')
insert into dateformatting values('1/2/1900')
insert into dateformatting values('1/2/1900 0:00')
SELECT CAST(Joiningdate AS datetime2) FROM dateformatting
-- Error: Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
Chris, Were you able to resolve this?
Unfortunately I could not understand the information from Andrew.
-LK
August 21, 2009 at 3:04 am
luckysql.kinda (8/21/2009)
-- Error: Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
The link i gave above gives full details of this. It really is the 'bible' in terms of dates in SQLServer.
August 21, 2009 at 3:40 am
Yes Dave. I agree.
The issue was a wrong date:
SELECT CAST('2011900' AS datetime) - This will fail..
-LK
August 21, 2009 at 3:50 am
Its not an issue of wrong date , its simply NOT a date.
Consider
insert into dateformatting values('2011900')
insert into dateformatting values('19000102')
It would be asking a hell of a lot of SQLServer to automagically decide if the format is
YYYYMMDD ,MMDDYYYY or DDMMYYYY
August 21, 2009 at 4:07 am
Yup
wrong date = simply NOT a date
-LK
August 21, 2009 at 5:13 am
Thanks guys for all your help, using the information provided I've managed to put together a working solution!
Your help is greatly appreciated.
www.sqlAssociates.co.uk
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply