Using ISNULL WITH DATADD - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Using ISNULL WITH DATADD

Author

Topic

s_anr
Yak Posting Veteran

81 Posts

Posted - 2009-07-04 : 09:17:54

How can I use isnull function with this query

select
b.name [Commodity],
dateadd(hh,-7,dateAdd(ss, close_date, '19700101'))[Close Date]
from time_table a
left outer join commodity b on a.code = b.id

Basically i want to display "PENDING" if close_date is NULL

I tried the following but it didn't work :

select
b.name [Commodity],
isnull(dateadd(hh,-7,dateAdd(ss, close_date, '19700101')), 'Pending')
from time_table a
left outer join commodity b on a.code = b.id

It givs the error : Conversion failed when converting datetime from character string

Any help would be apprciated.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-04 : 09:36:09

you can't have a column returning 2 different data type (datetime & string). This should be done in your front end application where you are displaying the data.

KH
[spoiler]Time is always against us[/spoiler]

s_anr
Yak Posting Veteran

81 Posts

Posted - 2009-07-04 : 09:52:33

ok this might help u to help me. the close date is in UNIX timstamp format as given below :

1246715159 should be interprted as 07/07/2009 07:15:59 PST

So, How do I use ISNULL function with this format?. I want the time to be dispalyed in mm/dd/yyyy hh:mm (24HR) format if its populatd else the word 'PENDING'. Any suggestions?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-04 : 09:56:44

use convert() with 103 and 108
example


select	convert(varchar(10), getdate(), 103) + ' ' + convert(varchar(5), getdate(), 108)

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-04 : 09:59:36

[code]select isnull(convert(varchar(10), close_date, 103) + ' ' + convert(varchar(5), close_date, 108), 'PENDING')[/code]

KH
[spoiler]Time is always against us[/spoiler]

s_anr
Yak Posting Veteran

81 Posts

Posted - 2009-07-04 : 10:05:11

Select convert(varchar(10), getdate(), 103) + ' ' + convert(varchar(5), getdate(), 108)

1. The above statement works with Getdate but not when I specify the table name like below (it gives me the UNIX timstamp):

convert(varchar(10), close_date, 103) + ' ' + convert(varchar(5), close_date, 108)

2. and how do i use ISNULL function with this format please...

s_anr
Yak Posting Veteran

81 Posts

Posted - 2009-07-04 : 10:11:24

I replied without checking the nxt reply. :)

With the statement given, ISNULL part works fine but i still don't se the date format correctly. AM I doing something wrong?

I tried this :
isnull(convert(varchar(10), a.close_date, 103) + ' ' + convert(varchar(5), a.close_date, 108), 'PENDING'),

It gives me something like 1246492349 * for the dates (which is not what i want) and PENDING for the NULLS (which is correct)

s_anr
Yak Posting Veteran

81 Posts

Posted - 2009-07-04 : 10:17:26

just to remphasis, th clos dat is in UNIX time stamp (http://www.epochconverter.com)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-04 : 10:58:06

[code]select
isnull(
convert(varchar(10), dateadd(ss,a.close_date,'1970-01-01'), 103)
+ ' '
+ convert(varchar(5), dateadd(ss,a.close_date,'1970-01-01'), 108)
, 'PENDING') as [Close Date]
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

s_anr
Yak Posting Veteran

81 Posts

Posted - 2009-07-04 : 11:43:39

Thanks Webfred. That was beautiful. Thanks Again.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-04 : 11:48:19

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

s_anr
Yak Posting Veteran

81 Posts

Posted - 2009-07-04 : 12:02:28

one more query webfred :

I'm trying to concatenate first and last name (stored in two different columns ) as given Below :

(isnull(e.first_name, '') + ' ' + isnull(e.last_name, 'NoName')) [FULL NAME]

The output should be First_Name Last_Name if the records are present else 'NoName'

I get an xtra space before 'Noname' if i use the above statement. The output bcomes ' Noname' instead of 'NoName'

Any Suggestions?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-04 : 12:23:41

[code]
replace((isnull(e.first_name, '') + ' ' + isnull(e.last_name, 'NoName')),' NoName','NoName') as [FULL NAME]
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-04 : 12:46:13

And to close the deal with the PST time zone,

COALESCE(CONVERT(CHAR(10), DATEADD(SECOND, Col1, '19691231 19:00'), 101) + ' ' + CONVERT(CHAR(5), DATEADD(SECOND, Col1, '19691231 19:00'), 108), 'Pending')

Microsoft SQL Server MVP

N 56°04'39.26"
E 12°55'05.63"

s_anr
Yak Posting Veteran

81 Posts

Posted - 2009-07-04 : 12:46:50

Webfred, Two things.

1. I screwed it up. Sorry!!

I get an xtra space before 'Last_Name' if i use the above statement. The output bcomes ' LastName' instead of 'LastName'
e.g. if first name is blank and last name is 'Smith', it gives the results as ' Smith'

2. (a) In the last thing about unix tim conversion with issnull, your query has 7 hrs differnce from actual time. y so?

So where the result should be 07/03/2009 00:48 , it becomes 03/07/2009 07:48
(b) the result is in DD/MM/YYYY HH:MM (24) instead of MM/DD/YYYY HH:MM (24)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-04 : 12:48:01

See post made 07/04/2009 : 12:46:13

Microsoft SQL Server MVP

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-04 : 12:49:45

COALESCE(e.First_Name, '') + COALESCE(' ' + e.Last_Name, '') AS FullName

Microsoft SQL Server MVP

N 56°04'39.26"
E 12°55'05.63"

s_anr
Yak Posting Veteran

81 Posts

Posted - 2009-07-04 : 12:56:19

I din't see the previous one before posting. Sorry abt that.

The date part still has 2 hrs difference
e.g. 07/03/2009 02:48 should actually be 07/03/2009 00:48

PS: The time on the machine is PST.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-04 : 13:02:20

Change "19:00" to "17:00" instead.

Microsoft SQL Server MVP

N 56°04'39.26"
E 12°55'05.63"

s_anr
Yak Posting Veteran

81 Posts

Posted - 2009-07-04 : 13:07:05

Thanks.. The Date part works bautifully now..