SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Data from multiple tables if values are Null
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

archana23
Yak Posting Veteran

74 Posts

Posted - 07/01/2013 :  10:52:12  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 07/01/2013 :  10:54:09  Show Profile  Reply with Quote
Coalesce(pdmregvalues.columnName,EdmRegvalues.ColumnName)

Cheers
MIK
Go to Top of Page

archana23
Yak Posting Veteran

74 Posts

Posted - 07/01/2013 :  11:39:42  Show Profile  Reply with Quote
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
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 07/01/2013 :  11:43:44  Show Profile  Reply with Quote
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
Go to Top of Page

archana23
Yak Posting Veteran

74 Posts

Posted - 07/01/2013 :  14:17:41  Show Profile  Reply with Quote
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
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 07/01/2013 :  14:37:45  Show Profile  Reply with Quote
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

Edited by - MIK_2008 on 07/01/2013 14:39:07
Go to Top of Page

archana23
Yak Posting Veteran

74 Posts

Posted - 07/01/2013 :  15:41:31  Show Profile  Reply with Quote
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
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 07/02/2013 :  07:38:19  Show Profile  Reply with Quote
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

Edited by - MIK_2008 on 07/02/2013 07:38:50
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/02/2013 :  07:50:55  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000