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.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 converting '0' to Time

Author  Topic 

umertahir
Posting Yak Master

154 Posts
Posted - 2009-02-27 : 04:52:47
How do I convert '0' which comes from the excel file as a text into time format?

My this part runs fine if the time is in correct format:

SELECT CONVERT(DATETIME,'1-Aug-04') + CONVERT(DATETIME, '00:00:00')

Result:
2004-08-01 00:00:00.000

but when it finds 0

SELECT CONVERT(DATETIME,'1-Aug-04') + CONVERT(DATETIME, '0')

Result:
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts
Posted - 2009-02-27 : 05:22:57
you could use ISDATE function to check if data is in datettime format, else replace it with a value of your choice.
Also, CONVERT(DATETIME, 0) should work fine.
Go to Top of Page

heavymind
Posting Yak Master

115 Posts
Posted - 2009-02-27 : 05:27:22

SELECT CONVERT(DATETIME,'1-Aug-04') + ISNULL(convert(datetime, NULLIF('0' /*replace with your value*/, '0')), '00:00:00')


Thanks, Vadym
MCITP DBA 2005/2008
Chief DBA at http://www.db-staff.com
Go to Top of Page

umertahir
Posting Yak Master

154 Posts
Posted - 2009-02-27 : 05:29:44
I am already have a check for time but the code still comes down to the part where I am combining the Date and Time together. I have now added where clause when I join them together so that should solve the problem. 

									tM.datMeasurementDate	= (CASE 
																	WHEN -- When both date and time in valid format
																		ISDATE(@Date) = 1 AND ISDATE(@Time) = 1
																	THEN
																		( CONVERT(DATETIME, @Date) + CONVERT(DATETIME, @Time) ) 

																	WHEN -- When only date in valid format
																		ISDATE(@Date) = 1 AND ISDATE(@Time) <> 1
																	THEN
																		( CONVERT(DATETIME, @Date) + CONVERT(DATETIME, '00:00:00.000') ) 

																	ELSE
																		NULL

Thanks for your reply.
quote:
Originally posted by sakets_2000

you could use ISDATE function to check if data is in datettime format, else replace it with a value of your choice.
Also, CONVERT(DATETIME, 0) should work fine.
Go to Top of Page

umertahir
Posting Yak Master

154 Posts
Posted - 2009-02-27 : 05:36:18
That's better approach, now my code is:


									tM.datMeasurementDate	= (CASE 
																	WHEN -- When both date and time in valid format
																		ISDATE(@Date) = 1 AND ISDATE(@Time) = 1
																	THEN
																		( CONVERT(DATETIME, @Date) + CONVERT(DATETIME, @Time) ) 

																	WHEN -- When only date in valid format
																		ISDATE(@Date) = 1 AND ISDATE(@Time) <> 1
																	THEN
																		( CONVERT(DATETIME, @Date) + ISNULL(CONVERT(DATETIME, NULLIF('0', @Time)), '00:00:00.000') ) 

																	ELSE
																		NULL


cheers
quote:
Originally posted by heavymind


SELECT CONVERT(DATETIME,'1-Aug-04') + ISNULL(convert(datetime, NULLIF('0' /*replace with your value*/, '0')), '00:00:00')


Thanks, Vadym
MCITP DBA 2005/2008
Chief DBA at http://www.db-staff.com
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-02-27 : 05:42:50
There is absolutely no need to add CONVERT(DATETIME, 0) because dates already has 00:00:00 as time part information!
tM.datMeasurementDate =	CASE
				WHEN -- When both date and time in valid format
					ISDATE(@Date) = 1 AND ISDATE(@Time) = 1
				THEN    CONVERT(DATETIME, @Date) + CONVERT(DATETIME, @Time)
				WHEN -- When only date in valid format
					ISDATE(@Date) = 1
				THEN    CONVERT(DATETIME, @Date)
				ELSE	NULL
			END


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

umertahir
Posting Yak Master

154 Posts
Posted - 2009-02-27 : 05:49:34
The reason why I have to convert it to datetime is because the 0 comes from excel as a text which will be a char not INT so then I get conversion error.
quote:
Originally posted by Peso

There is absolutely no need to add CONVERT(DATETIME, 0) because dates already has 00:00:00 as time part information!
tM.datMeasurementDate =	CASE
				WHEN -- When both date and time in valid format
					ISDATE(@Date) = 1 AND ISDATE(@Time) = 1
				THEN    CONVERT(DATETIME, @Date) + CONVERT(DATETIME, @Time)
				WHEN -- When only date in valid format
					ISDATE(@Date) = 1
				THEN    CONVERT(DATETIME, @Date)
				ELSE	NULL
			END


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-02-27 : 05:54:11
I repeat, you don't have to add time information since datetime already includes 00:00:00 for you.
See this example
DECLARE	@Sample TABLE
	(
		dateCol VARCHAR(20),
		timeCol VARCHAR(20)
	)

INSERT	@Sample
SELECT	'2009-02-27', '11:55:24' UNION ALL
SELECT	'39869', '11:55:24' UNION ALL
SELECT	'2009-02-27', '0' UNION ALL
SELECT	'39869', '0'

SELECT	dateCol,
	timeCol,
	CASE
		WHEN ISDATE(dateCol) = 1 AND ISDATE(timeCol) = 1 THEN CONVERT(DATETIME, dateCol) + CONVERT(DATETIME, timeCol)
		WHEN ISDATE(dateCol) = 1 THEN CONVERT(DATETIME, dateCol)
		ELSE NULL
	END AS Peso
FROM	@Sample

E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

umertahir
Posting Yak Master

154 Posts
Posted - 2009-02-27 : 06:05:29
oh right... I know what you mean now, thanks for taking complexity out of my code.

WHEN -- When only date in valid format
ISDATE(@Date) = 1 AND ISDATE(@Time) <> 1 AND @Time = '0'
THEN
CONVERT(DATETIME, @Date)

quote:
Originally posted by Peso

I repeat, you don't have to add time information since datetime already includes 00:00:00 for you.
See this example
DECLARE	@Sample TABLE
	(
		dateCol VARCHAR(20),
		timeCol VARCHAR(20)
	)

INSERT	@Sample
SELECT	'2009-02-27', '11:55:24' UNION ALL
SELECT	'39869', '11:55:24' UNION ALL
SELECT	'2009-02-27', '0' UNION ALL
SELECT	'39869', '0'

SELECT	dateCol,
	timeCol,
	CASE
		WHEN ISDATE(dateCol) = 1 AND ISDATE(timeCol) = 1 THEN CONVERT(DATETIME, dateCol) + CONVERT(DATETIME, timeCol)
		WHEN ISDATE(dateCol) = 1 THEN CONVERT(DATETIME, dateCol)
		ELSE NULL
	END AS Peso
FROM	@Sample

E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-02-27 : 06:09:14
Why do you persist in doing things harder then necessary.
WHEN-clauses are executed in top-to-bottom order anyway, so if you already have checked for both date and time is valid, and you on next row only check for date valid, that is enough, becuase the other combination has already been checked.
So in next row you only have to check for date valid.

Exactly as in my example.


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

umertahir
Posting Yak Master

154 Posts
Posted - 2009-02-27 : 06:22:11
I have added the part @Time = '0' because if the user's intention is to put 0 in and no other value has been mistyped in the field only then make it to 0 otherwise consider the whole two fields as NULL.

so if @Time = asdas then it won't be taken then

quote:
Originally posted by Peso

Why do you persist in doing things harder then necessary.
WHEN-clauses are executed in top-to-bottom order anyway, so if you already have checked for both date and time is valid, and you on next row only check for date valid, that is enough, becuase the other combination has already been checked.
So in next row you only have to check for date valid.

Exactly as in my example.


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-02-27 : 06:27:09
[code]DECLARE @Sample TABLE
(
dateCol VARCHAR(20),
timeCol VARCHAR(20)
)

INSERT @Sample
SELECT '2009-02-27', '11:55:24' UNION ALL
SELECT '2009-02-27', '0' UNION ALL
SELECT '2009-02-27', 'asdas' UNION ALL
SELECT '39869', '11:55:24' UNION ALL
SELECT '39869', '0' UNION ALL
SELECT '39869', 'asdas' UNION ALL
SELECT NULL, '11:55:24' UNION ALL
SELECT NULL, '0' UNION ALL
SELECT NULL, 'asdas' UNION ALL
SELECT 'asdas', '11:55:24' UNION ALL
SELECT 'asdas', '0' UNION ALL
SELECT 'asdas', 'asdas'

SELECT dateCol,
timeCol,
CASE
WHEN ISDATE(dateCol) = 1 AND ISDATE(timeCol) = 1 THEN CONVERT(DATETIME, dateCol) + CONVERT(DATETIME, timeCol)
WHEN ISDATE(dateCol) = 1 THEN CONVERT(DATETIME, dateCol)
ELSE NULL
END AS Peso
FROM @Sample


dateCol timeCol Peso
---------- -------- -----------------------
2009-02-27 11:55:24 2009-02-27 11:55:24.000
2009-02-27 0 2009-02-27 00:00:00.000
2009-02-27 asdas 2009-02-27 00:00:00.000
39869 11:55:24 NULL
39869 0 NULL
39869 asdas NULL
NULL 11:55:24 NULL
NULL 0 NULL
NULL asdas NULL
asdas 11:55:24 NULL
asdas 0 NULL
asdas asdas NULL[/code]
E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

umertahir
Posting Yak Master

154 Posts
Posted - 2009-02-27 : 06:34:18
Ok, now my final code is:

tM.datMeasurementDate	= 
(CASE 
WHEN -- When both date and time in valid format
  ISDATE(@Date) = 1 AND ISDATE(@Time) = 1
THEN
  ( CONVERT(DATETIME, @Date) + CONVERT(DATETIME, @Time) ) 

WHEN -- When only date in valid format
  ISDATE(@Date) = 1
THEN
  CONVERT(DATETIME, @Date) 

ELSE
  NULL
																END


Thanks a lot man.
quote:
Originally posted by Peso

DECLARE	@Sample TABLE
	(
		dateCol VARCHAR(20),
		timeCol VARCHAR(20)
	)

INSERT	@Sample
SELECT	'2009-02-27', '11:55:24' UNION ALL
SELECT	'2009-02-27', '0' UNION ALL
SELECT	'2009-02-27', 'asdas' UNION ALL
SELECT	'39869', '11:55:24' UNION ALL
SELECT	'39869', '0' UNION ALL
SELECT	'39869', 'asdas' UNION ALL
SELECT	NULL, '11:55:24' UNION ALL
SELECT	NULL, '0' UNION ALL
SELECT	NULL, 'asdas' UNION ALL
SELECT	'asdas', '11:55:24' UNION ALL
SELECT	'asdas', '0' UNION ALL
SELECT	'asdas', 'asdas'

SELECT	dateCol,
	timeCol,
	CASE
		WHEN ISDATE(dateCol) = 1 AND ISDATE(timeCol) = 1 THEN CONVERT(DATETIME, dateCol) + CONVERT(DATETIME, timeCol)
		WHEN ISDATE(dateCol) = 1 THEN CONVERT(DATETIME, dateCol)
		ELSE NULL
	END AS Peso
FROM	@Sample


dateCol		timeCol		Peso
----------	--------	-----------------------
2009-02-27	11:55:24	2009-02-27 11:55:24.000
2009-02-27	0		2009-02-27 00:00:00.000
2009-02-27	asdas		2009-02-27 00:00:00.000
39869		11:55:24	NULL
39869		0		NULL
39869		asdas		NULL
NULL		11:55:24	NULL
NULL		0		NULL
NULL		asdas		NULL
asdas		11:55:24	NULL
asdas		0		NULL
asdas		asdas		NULL

E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-02-27 : 06:36:25
You're welcome.

I hope you understand the nature of CASE ... END block now.


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

umertahir
Posting Yak Master

154 Posts
Posted - 2009-02-27 : 06:44:17
You're a star.

cheers

quote:
Originally posted by Peso

You're welcome.

I hope you understand the nature of CASE ... END block now.


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts
Posted - 2009-02-27 : 08:06:50
He's a saint, actually
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2009-03-02 : 04:40:42
But note that ISDATE() is not reliable

SELECT ISDATE(2000)

You need to check the length too
From Peso's code

	CASE
		WHEN ISDATE(dateCol) = 1 AND LEN(datecol)>=8 AND ISDATE(timeCol) = 1 THEN CONVERT(DATETIME, dateCol) + CONVERT(DATETIME, timeCol)
		WHEN ISDATE(dateCol) = 1 AND LEN(datecol)>=8 THEN CONVERT(DATETIME, dateCol)
		ELSE NULL
	END


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site