SQL Datetime again 2

[chester27]

I have a holding table with imported data from an external source. All columns are set to Varchar(50). When I try and run a script that takes this data and populates the correct table I get "conversion failed when converting to datetime error" data in Varchar field reads 22/11/2008 00:00:00 (just one of over a million different dates) and the column in correct table set to datetime. server is sql 2005 and script looks like this...

insert into xxxx
(testtable)

select

--data
CASE [Column 6] WHEN null THEN null WHEN '' THEN null ELSE Convert(datetime, [Column 6], 104) END,
from upload

 

[hopper44]

It looks like there are some rows in your table that do not conform to the 22/11/2008 00:00:00 format. Hence, the error is generated.

To verify if that is the problem, try to put a TOP statement to select, say 2 rows, and verify if that works.
For example,

select TOP 2
CASE [Column 6]
WHEN null THEN null
WHEN '' THEN null
ELSE Convert(datetime, [Column 6], 104)
END from upload

If this works, then you would have to check which row(s) in the table do not conform to the 104 format.

 

[SQLBill]

You can use the ISDATE function to find out if there are any values that are not date values.

Also, depending on what your SQL Server collation is set to, it might be looking for the date value as mm/dd/yyyy not dd/mm/yyyy. Use SET DATEFORMAT DMY

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[gmmastros]

SQLBill,

The collation has nothing to do with it. The date interpretation settings are controlled by the default language of the login.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLBill]

That's it George...I knew it was something like that. I was responding with much too little sleep this morning. I appreciate you correcting my error.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[sparkbyte]

Played with your suggestion a bit and here is what I came up with as an example.

Create Table #tempTable 
(
	Table_ID Int,
	DateIn VarChar(50)
)

Insert Into #tempTable (Table_ID, DateIn)
	Select 1, '22/11/2008 00:00:00' union all
	Select 2, '23/11/2008 00:00:00' union all
	Select 3, '24/11/2008 00:00:00' union all
	Select 4, '25/11/2008 00:00:00' union all
	Select 5, '22/10/2008 00:00:00' union all
	Select 6, '22/09/2008 00:00:00' union all
	Select 7, '22/08/2008 00:00:00' union all
	Select 8, '' union all
	Select 9, Null 
	
SET DATEFORMAT DMY
Select 
	Table_ID,
	DateIn,
		CASE IsDate(DateIn)
			When 0 Then 'False'
			When 1 Then 'True'
		End As Is_Date
from #tempTable

Select 
	Table_ID,
	DateIn
from #tempTable
Where isdate(Datein) = 1 -- 0 equels False, 1 equals True

Drop Table #tempTable

Output 1

(9 row(s) affected)
Table_ID    DateIn                                             Is_Date
----------- -------------------------------------------------- -------
1           22/11/2008 00:00:00                                True
2           23/11/2008 00:00:00                                True
3           24/11/2008 00:00:00                                True
4           25/11/2008 00:00:00                                True
5           22/10/2008 00:00:00                                True
6           22/09/2008 00:00:00                                True
7           22/08/2008 00:00:00                                True
8                                                              False
9           NULL                                               False

Output 2

(9 row(s) affected)

Table_ID    DateIn
----------- --------------------------------------------------
1           22/11/2008 00:00:00
2           23/11/2008 00:00:00
3           24/11/2008 00:00:00
4           25/11/2008 00:00:00
5           22/10/2008 00:00:00
6           22/09/2008 00:00:00
7           22/08/2008 00:00:00

Thanks

John Fuhrman

 

[chester27]

Sorry for the confusion in advance, have had a closer look and it seems that the datetime given is 23-12-1991 00:00:00 and I am trying to update this into a datetime column and change it to 23/12/1991 00:00:00.
If I leave as 23-12-1991 00:00:00 it works in the DB but application expects the / so tried this and get the conversion error.

update icwsys.PMIUpload set [coloumn 1] = Convert(datetime, substring([coloumn 1], 1, 2) + '/' + substring([coloumn 1], 3, 2) + '/' + substring([coloumn 1], 5, 4), 104)

 

[SQLBill]

If the value in the database is dd-mm-yyyy hh:mm:ss and you need dd/mm/yyyy hh:mm:ss, why not just update the table and use REPLACE to change the - to a / ??

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[sparkbyte]

Why not just replace the '-' in the date?

update icwsys.PMIUpload
set [coloumn 1] = Replace([coloumn 1], '-', '/')



Thanks

John Fuhrman

 

[chester27]

This is driving me mad. Now finding dates as 1991-11-23. All these dates are in a VarChar column and need to update them to datetime in the new column.

 

[SQLBill]

Unfortunately, that is the issue with putting dates/times into VARCHAR/CHAR columns. If you are going to do that you really need to have the application validate the values so they are all the same format.

SQL Server won't be able to convert the values to DATETIME until they are all the same format.

At this point, your only option is to go through the values and update them all to be in one format. After that is done then you will be able to convert them to DATETIME.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[chester27]

All sorted, found a rouge entry, and used the set dateformat to dmy then used the convert(xxxxx, 120) thanks for all the help.