Author |
Topic |
patshaw
Posting Yak Master
177 Posts |
Posted - 2006-10-27 : 08:47:39
|
Hi,The following code:SELECT ISNULL(s.LogOnTime,'00:00:00) as LogOnTimeFROM usersreturns:1900-01-01 00:00:00.000for the column where it is null. Any other value I use just returns an error. Is there any way I can get the column to return '00:00:00' where it is null? |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-10-27 : 08:53:57
|
[code]SELECT ISNULL(s.LogOnTime,'00:00:00) as LogOnTimeFROM users[/code]It's correct. Leave it as it is and format the LogOnTime in your front end.alternatively use convert with style 108 KH |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-10-27 : 09:00:00
|
Yes. But you have to cast all values to varchar, which is a BAD idea."Zero" date for SQL server is January 1, 1900.Peter LarssonHelsingborg, Sweden |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-10-27 : 09:00:30
|
>>Is there any way I can get the column to return '00:00:00' where it is null?Where do you want to show data?If you use '' or '00:00:00', you will get the same resultIf you use front end application, do this thereMadhivananFailing to plan is Planning to fail |
|
|
patshaw
Posting Yak Master
177 Posts |
Posted - 2006-10-27 : 09:28:09
|
I've been told by a colleague that this can be done as a UDF. Is this true and if so how can do I write the UDF. I have no experience with them. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-10-27 : 09:36:17
|
Tell your colleague he's/she's yanking your chains.This is so easy that no UDF is needed.First, have you read and understood all previous answers here?Second, use thisSELECT ISNULL(CONVERT(varchar, s.LogOnTime, 108), '00:00:00') as LogOnTimeFROM users Peter LarssonHelsingborg, Sweden |
|
|
patshaw
Posting Yak Master
177 Posts |
Posted - 2006-10-27 : 10:37:29
|
Hi Peso,Yes, I see now and it works great. This now presents me with another problem.The SELECT is also returning some non-NULL dates. These non-NULLs should be represented as '2006-10-27 10:11:19.177' format but since applying your solution above they are now returning as '27 Oct 2006 10:11:19:177' which I have been told is not acceptable for the client. I have tried various formatting options but cannot get the non-NULLS to show as '2006-10-27 10:11:19.177'. Can you advise me further on this please. |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-10-27 : 10:46:15
|
1. If you use Peter's code, the non-NULL dates will not return as '2006-10-27 10:11:19.177' but '10:11:19'. That's the formatted result of style 108.2. if the acceptable format is YYYY-MM-DD HH:MM:SS then you should use convert with style 121SELECT ISNULL(CONVERT(varchar(23), s.LogOnTime, 121), '00:00:00') as LogOnTimeFROM users EDIT :Please refer to the Books Online on CAST and CONVERT KH |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-10-27 : 10:48:20
|
Books Online is your friendSELECT ISNULL(CONVERT(varchar, s.LogOnTime, 121), '00:00:00') as LogOnTimeFROM users Peter LarssonHelsingborg, Sweden |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-10-27 : 10:49:07
|
Peter LarssonHelsingborg, Sweden |
|
|
patshaw
Posting Yak Master
177 Posts |
Posted - 2006-10-27 : 11:00:32
|
Much apprciated guys, Thanks. |
|
|
|