Data from multiple tables if values are Null

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

Data from multiple tables if values are Null

Author

Topic

archana23
Yak Posting Veteran

89 Posts

Posted - 2013-07-01 : 10:52:12

Hi,

Good morning.

I am getting RegisteredTime from one table called pdmregvalues PR

as like this

PR.response as RegisteredTime

but if above value is null we needto get data from another table called EdmRegvalues ER

DATEADD(ss,ER.StartRW,'1980-03-01 00:00:00.000') as RegisteredTime

I need to get RegisteredTime from either of above table first we need to check in PR table if it is null we need to get the value from ER table.

Can any one please help me on this for write the query?

Thank you

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-07-01 : 10:54:09

Coalesce(pdmregvalues.columnName,EdmRegvalues.ColumnName)

Cheers
MIK

archana23
Yak Posting Veteran

89 Posts

Posted - 2013-07-01 : 11:39:42

Hi MIK,

Thank you for your reply.

When i use like this

Coalesce(PR.response, DATEADD(ss,ER.StartRW,'1980-03-01 00:00:00.000')) as RegisteredTime

I am getting below error

"The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."

Since response is a varchar datatype column in PR table. So how can i avaoid this error?

Thank you.

Archana

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-07-01 : 11:43:44

Check if this stand alone works?

DATEADD(ss,ER.StartRW,'1980-03-01 00:00:00.000')

If it does .. explicitly convert both to Varchar.
e.g. Coalesce(CAST(PR.response as Varchar),CAST (DATEADD(ss,ER.StartRW,'1980-03-01 00:00:00.000') AS Varchar))

By the way .. try always using standard date/datetime datatype for date related information

Cheers
MIK

archana23
Yak Posting Veteran

89 Posts

Posted - 2013-07-01 : 14:17:41

It worked. Thank you so much for your help..

but we are getting CAST (DATEADD(ss,ER.StartRW,'1980-03-01 00:00:00.000') AS Varchar) values as

May 23 2013 8:42AM , May 24 2013 1:49PM

I want to get 2013-05-23 08:42:00.000 and 2013-05-24 13:49:00.000

How could i achive like this?

Thank you

Archana

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-07-01 : 14:37:45

that is SQL default date format, which could be achievable but you need to fix the erronous data if there is any. Since the error you mentioned "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value" shows that there is some data inside a table's field (either of pdmregvalues.columnName or EdmRegvalues.ColumnName).
e.g.
select CONVERT(datetime,'01012013') --won't work and will throw the conversion error.
select CONVERT(datetime,'20130201') -- will work
select CONVERT(datetime,'01/01/2013') --will work

You can find erronous data using isDate function
e.g.
select isdate('01012013') --0 means not a valid date
select isdate('20130201') -- 1 means a valid date and convertable
select isdate('01/01/2013') --1 means a valid date and convertable

Cheers
MIK

archana23
Yak Posting Veteran

89 Posts

Posted - 2013-07-01 : 15:41:31

Hi MIK,

We dont have any erronous data .Actual problem is

pdmregvalues.columnName -- DataType is Varchar(90) -- It stores only time as like this 0940,1810,1645 etc..

whereas EdmRegvalues.ColumnName -- DataType is int -- we are converting this int to Datetime as DATEADD(ss,EdmRegvalues.ColumnName ,'1980-03-01 00:00:00.000')

so when we are checking first value is Varchar and second value is dateTime so we are getting error

Archana

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-07-02 : 07:38:19

quote:
Originally posted by archana23

Hi MIK,

Thank you for your reply.

When i use like this

Coalesce(PR.response, DATEADD(ss,ER.StartRW,'1980-03-01 00:00:00.000')) as RegisteredTime

I am getting below error

"The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."

Since response is a varchar datatype column in PR table. So how can i avaoid this error?

Thank you.

Archana

I was talking about this error.

Anyhow, if you're sure that there is no issue in the data then convert the whole coalesce to a datetime datatype.

e.g.
Convert(Datetime,Coalesce(CAST(PR.response as Varchar),CAST (DATEADD(ss,ER.StartRW,'1980-03-01 00:00:00.000') AS Varchar)))

just a suggestion, this is all happending because of the incorrect data type usage when there is a valid one. Always use correct datatype for your structures unless there is compelling reason to override.

By the way, if you're doing all this for any application, then always try handling formatting things on application layer

Cheers
MIK

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-02 : 07:50:55

the problem with these varchar fields is unless you use proper style value while trying to convert to datetime, the values will be interpreted based on the language and dateformat settings of the server.

See the illustration in below link

http://visakhm.blogspot.com/2011/12/why-iso-format-is-recommended-while.html

Thats why its recommended to use unambiguos formats like ISO format (YYYYMMDD) while passing(storing) date values in a varchar field/parameter.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources