Hi,
I've an int field and when this field has a null, I want to show empty/blanks instead of 0. How can this be achieved?
I've tried the below:
declare @a as int
set @a= null
select isnull(@a,'')
o/p: 0
select cast(isnull(@a,'') as varchar)
o/p: 0
select case convert(varchar,@a)
when null then ''
else @a
end
o/p: null
Thanks for your help!
Subha
Aim- the following columns “Cancel_Date”, “First_Post_Date”,“Last_Post_Date” can either be populated with figures or have a null value.
I want all Null values to be removed and be replaced with a blank
Final table is
Select *
from #test
left join #SF_AccountBuild on #test.FDMSAccountNo = #SF_AccountBuild.[External FDMSaccountno]
Results
[FDMSAccountNo],
External_ID ,
Parentsfid,
[DBA Name],
[Legal Name],
Street,
[MM3-DBA-ADDR2],
[Code] .....
I have tried all possible combinations of changing this. But was not able to make the results tally.I am giving you a part of the query, there are others queries involving 4 tables which are based on this same temporary table query.
SELECT c.juris_id, b.jrnl_mo_yr
FROM a_trueup a,
#t_mths b, r_rj c
WHERE a.rlzd_mo_yr
=* b.jrnl_mo_yr
AND
a.juris_id
=* c.juris_id
[code]....
I tried using left outer join as mentioned in blogs but got a different result (14 rows).I also used set null off/on options but no luck ..
I have a pivot transform that pivots a batch type. After the pivot, each batch type has its own row with null values for the other batch types that were pivoted. I want to group two fields and max() the remaining batch types so that the multiple rows are displayed on one row. I tried using the aggregate transform, but since the batch type field is a string, the max() function fails in the package. Is there another transform or can I use the aggragate transform another way so that the max() will work on a string?
-- Ryan
I have a DTSX package which reads values from a fixed-length text file using a data reader and writes some of the column values from the file to an Oracle table. We have used this DTSX several times without incident but recently the process started inserting NULL values for some of the columns when there was a valid value in the source file. If we extract some of the rows from the source file into a smaller file (i.e 10 rows which incorrectly returned NULLs) and run them through the same package they write the correct values to the table, but running the complete file again results in the NULL values error. As well, if we rerun the same file multiple times the incidence of NULL values varies slightly and does not always seem to impact the same rows. I tried outputting data to a log file to see if I can determine what happens and no error messages are returned but it seems to be the case that the NULL values occur after pulling in the data via a Data Reader. Has anyone seen anything like this before or does anyone have a suggestion on how to try and get some additional debugging information around this error?
View 12 Replies View RelatedI have SQL Server 2012 SSIS. I have Excel source and OLE DB Destination.I have problem with importing CustomerSales column.CustomerSales values like 1000.00,2000.10,3000.30,NotAvailable.So I have decimal values and nvarchar mixed in on Excel column. This is requirement for solution.However SSIS reads only numeric values correctly and nvarchar values are set as Null. Why?
CREATE TABLE [dbo].[Import_CustomerSales](
[CustomerId] [nvarchar](50) NULL,
[CustomeName] [nvarchar](50) NULL,
[CustomerSales] [nvarchar](50) NULL
) ON [PRIMARY]
Hi
can somebody explain me how I can assign a NULL value to a datetime type field in the script transformation editor in a data flow task.
In the script hereunder, Row.Datum1_IsNull is true, but still Row.OutputDatum1 will be assigned a value '0001-01-01' which generates an error (not a valid datetime). All alternatives known to me (CDate("") or Convert.ToDateTime("") or Convert.ToDateTime(System.DBNull.Value)) were not successful.
Leaving out the ELSE clause generates following error: Error: Year, Month, and Day parameters describe an un-representable DateTime.
If Not Row.Datum1_IsNull Then
Row.OutputDatum1 = Row.Datum1
Else
Row.OutputDatum1 = CDate(System.Convert.DBNull)
End If
Any help welcome.
Hi,
My query "select blah, blah, rank from tablewithscores" will return results that can legitimately hold nulls in the rank column. I want to order on the rank column, but those nulls should appear at the bottom of the list
e.g.
Rank Blah Blah
1 - -
2 - -
3 - -
NULL - -
NULL - -
At present the NULLs are at the top of the list, but I do not want my ranking in descending order. Any suggestions?
Thanks
Dan
I'm using UPDATE as follows"
UPDATE Dubai
SET DB = 'D'
WHERE DB = NULL
but nothing happens. What am I doing wrong?
Thanks.
I am using the following query to calculate date differences:select ..........DATEDIFF(d, recruitment_advertising.advertising_date, career_details.RTS_Email AS Datetime) AS Ad_to_RTS_days FROM .....I have stored all my dates as NVARCHAR because of the issues with localization.If the value is an empty String my output is eg: -38700. which is way off and incorrect. Some of the values in my table are NULL and they produce the correct result.Is there a T-SQL statement to replace empy Strings with the NULL value in my tables.I'd like to use it as a trigger when inserting or updating to convert empty strings to NULLbefore the values are inserted.Thanks guys.
View 1 Replies View RelatedI have an SSIS package that imports data from an Excel file, replaces any value in Excel that reads "NULL" to "", then writes the data to a couple of databases.
What I have discovered today, is I have two columns of dates, an admit date and discharge date column, and what I need to do is anywhere I have a null value in the discharge date column, I have to replace it with the value in the admit date column.
I have searched around online and tried a few things using the Replace funtion in Derived columns but no dice so far.
Is there any function in SQL Server to convert INT Values to HEX
Ex IF Int Value = 33948700 ,then I shud get HEX Value = 206041C
Thx All for any help
Venu
I have to convert values like 0x01A4 to integer and I just can not get it right!
Select Convert(int, 0x01A4) works OK but is not useful.
But
Declare @hex varchar(20)
Select Convert(int, @b) yields:
Server: Msg 245, Level 16, State 1, Line 13
Syntax error converting the varchar value '0x01A4' to a column of data type int.
What's the trick??
I have a column that is frequently updated with an append query. I need to turn all of the incoming positive values into negative values (as well as convert all of the existing). I tried to multiply the column by -1 in the formula option in table design view but couldn't find the proper formula format.
Can anyone suggest an approach.
I have a columns with datatype varchar(50) which has date values in it.
I am trying to convert the values in the column to a date value,but i am getting conversion error.
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
select last_update from importtest where convert(datetime,last_update)<convert(datetime,'24 Dec 2014')
Not sure how I can convert varchar to numeric values in SQL Server? Trying to do so in Design and get error:
Unable to modify table.
ODBC error: [Microsoft][ODBC Server Driver][SQL Server] Error converting data type varchar to numeric.
Any suggestions?
Hi. I I'm importing a text file with lot's of decimal values with this format xx.xx. The problem is that my locale is Portugal and the points are being striped off and are not being considered as decimal separators (for example I have values like 0.04 and in the sql server database i see 4). I have tried to change the locale but i receive a message saying that the locale is not installed in my system.
Any help on this ? tnks in advance
We have inherited an appointments database that has a table tblAppointments
Within this table there are 2 fields, ApptFrom & ApptTo, these are the appointment start & finish times.
My problem is, the values in these fields are held as integers ranging from 0 - 288. I have worked out the scale for the integers, it is based on a 24hr clock with each segment representing 5mins
Eg: 0 = 0:00
12 = 1.00
24 = 2.00
36 = 3.00
...
288 = 24:00
What I need is a piece of T_SQL that will translate these into times that can be read by a normal user
So, say I have a record with an ApptFrom = 36 & ApptTo = 42, I need to be able to show these as ApptFrom = 3.00 & ApptTo = 3.30
Any help is greatly appreciated.
Cheers,
Craig
I have a field that was text YESNONull. When I imported it into SQL I converted it to Boolean and had to go back and null out the ones that were supposed to be null. I am now trying to use the fields in my old reports and find that I have to convert the values to YesNo inside the query. This causes me to have to change almost every report. So I am wondering if there's a easier way to return YesNoNull or should I just keep the field as a 3 byte text?
Code:
SELECT Jobs_Table.JobNum,
[Jobs_Table].[Basic]+[jobs_Table].[FullService] AS MailPieces,
The_Big_One1.HT_Mail_STId,
IIf(IsNull([The_Big_One1].[First_Scan_Date]),"",IIf([The_Big_One1].[STC_SCAN]=True,"Yes","No")) AS STC,
[Code] ....
or
Code:
SELECT Jobs_Table.JobNum,
[Jobs_Table].[Basic]+[jobs_Table].[FullService] AS MailPieces,
The_Big_One1.HT_Mail_STId,
THE_Big_One1.STC_SCAN,
[Code] ...
Hi,
I have a calendar parameter in one of my reports. The values of the calendar are of the form 'yyyy-mm-dd hh:mms'. And they are string values.
Now when I generate the report I have a textbox that takes the parameter label (e.g., parameter!calendar.label). However, I would like to format (or trim) the label as just 'yyyy-mm-dd'. Does anyone know how I can do this? I tried to change the parameter value format to datetime but this just throws an invalid datatype error...
cheers,
Al
As a DBA, I am working on a project where an ETL process(SSIS) takes a long time to aggregate and process the raw data.
I figured out few things where the package selects the data from my biggest 200 GB unpartitioned table which has a datekey column but the package converts its each row to an integer value leading to massive scans and high CPU.
Example: the package passed two values 20140714 and 4 which means it wants to grab data from my biggest table which belongs between 20140714 04:00:00 and 20140714 05:00:00.
It leads to massive implicit conversions and I am trying to change this.
To minimize the number of changes, what I am trying to do is to convert 20140714 and 4 to a datetime format variable.
Select Convert(DATETIME, LEFT(20170714, 8)) which gives me a date value but I am stuck at appending time(HH:00:00) to it.
I would like to convert a UTC datetime value to any Local Time Zone. Below is some code I'm trying to ensure works correctly. Anyone care to comment?
I have used the following lines before the function to obtain the 2nd parameter:
SELECT GETUTCDATE()
SELECT GETDATE()
SELECT DATEDIFF(hh,'2008-05-20 20:08:01.020', '2008-05-20 16:08:01.020')
DECLARE @UTC DATETIME
EXECUTE @UTC = UTCtoLocalDate '2008-05-20 20:08:01.020', -4
-- =============================================
-- Description: <Converts local datetime values to UTC datetime values, using the built-in GETUTCDATE() function.
-- <Parameter two (@TZ) represents the time zone difference from UTC/GMT. To obtain this value
-- <run GETUTCDATE() and GETDATE(), then determine the timezone from the difference between the two.>
-- =============================================
CREATE FUNCTION [dbo].[UTCtoLocalDate] (@UTCDate DATETIME, @TZ INT)
RETURNS DATETIME AS
BEGIN
IF ( DATEPART(hh, @UTCDate) <> 0 )
BEGIN
DECLARE @LocalDate DATETIME
DECLARE @UTCDelta INT
DECLARE @thisYear INT
DECLARE @DSTDay INT
DECLARE @NormalDay INT
DECLARE @DSTDate DATETIME
DECLARE @NormalDate DATETIME
SET @thisYear = YEAR(@UTCDate)
IF (@thisYear < 2007 )
BEGIN
SET @DSTDay = ( 2 + 6 * @thisYear - FLOOR(@thisYear / 4) ) % 7 + 1
SET @NormalDay = ( 31 - ( FLOOR( @thisYear * 5 / 4) + 1) % 7)
SET @DSTDate = '4/' + CAST(@DSTDay AS VARCHAR(2)) + '/' + CAST(@thisYear AS VARCHAR(4)) + ' 2:00:00.000 AM'
SET @NormalDate = '10/' + CAST(@NormalDay AS VARCHAR(2)) + '/' + CAST(@thisYear AS VARCHAR(4)) + ' 2:00:00.000 AM'
END
ELSE
BEGIN
SET @DSTDay = ( 14 - ( FLOOR( 1 + @thisYear * 5 / 4 ) ) % 7 )
SET @NormalDay = ( 7 - ( FLOOR ( 1 + @thisYear * 5 / 4) ) % 7 )
SET @DSTDate = '3/' + CAST(@DSTDay AS VARCHAR(2)) + '/' + CAST(@thisYear AS VARCHAR(4)) + ' 2:00:00.000 AM'
SET @NormalDate = '11/' + CAST(@NormalDay AS VARCHAR(2)) + '/' + CAST(@thisYear AS VARCHAR(4)) + ' 2:00:00.000 AM'
END
IF ((@UTCDate > @DSTDate) AND (@UTCDate < @NormalDate))
BEGIN
SET @UTCDelta = @TZ + 1
END
ELSE
BEGIN
SET @UTCDelta = @TZ
END
-- now convert utc date to local date
SET @LocalDate = DATEADD(Hour, @UTCDelta, @UTCDate)
END
ELSE
BEGIN
SET @LocalDate = @UTCDate
END
RETURN(@LocalDate)
END
GO
Hi, how do I convert dates with SSIS, when I receive dates I have no problem, with empty fields I have to go through a transformation as:
trim(field_name) == "" ? "999999" : substring....... (derive transform)
after this I have to update each field holding '999999' to NULL. (using sql task)
Is there an easier way to doing this?
Thanks
Hi, I ve a table, I want to fetch certain rows based on the value of a Column. That column is nullable, and contains NULL values.I used the following query,SELECT Col_A FROM TABLE1 WHERE SOME_ID = 1317 AND Col_B NOT IN (8,9) Here, Col_B contains NULL values too. I need to fetch all rows where Col_B is not 8 or 9.Now, if I use "NOT IN", it does not work. I tried reading on it and got to know why it does not work. Even "NOT EXISTS" does not help. But still I've to fetch my values. How do I do that?Thanks & Regards,Jahanzeb
View 6 Replies View RelatedI have tried doing a search, as I figured this would be a common problem, but I wasn't able to find anything. I know that my SP is functional because when I use VWD execute the query outside of the webpage, I get the correct results -however I have to ensure that a field is either entered, or set to <NULL>. In my SET's I want it to use the wildcards.
What I want is to do a search (plenty of existing topics on that, however none were of help to me). If a field is entered, then it is included in the search. Otherwise it should be ignored. In my VB I have the standard stored procedure call, passing in values to all of the parameters in the stored proc below:
CREATE PROCEDURE dbo.SearchDog@tagnum int,@ownername varchar(50), @mailaddress varchar(50),@address2 varchar(50),@city varchar(50),@telephone varchar(50),@doggender varchar(50),@dogbreed varchar(50),@dogage varchar(50),@dogcolour varchar(50),@dogname varchar(50),@applicationdate varchar(50)AS IF @tagnum=-1 SET @tagnum=NULL SET @ownername = '%'+@ownername+'%' SET @mailaddress = '%'+@mailaddress+'%' SET @address2='%'+@address2+'%' SET @city = '%'+@city+'%' SET @telephone='%'+@telephone+'%' SET @dogcolour='%'+@dogcolour+'%' SET @dogbreed='%'+@dogbreed+'%' SET @dogage='%'+@dogage+'%' SET @doggender='%'+@doggender+'%' SET @dogname='%'+@dogname+'%' SET @applicationdate='%'+@applicationdate+'%'
SELECT DISTINCT * FROM DogRegistry WHERE ( TagNum = @tagnum OR OwnerName LIKE @ownername OR MailAddress LIKE @mailaddress OR Address2 LIKE @address2 OR City LIKE @city OR Telephone LIKE @telephone OR DogGender LIKE @doggender OR DogBreed LIKE @dogbreed OR DogAge LIKE @dogage OR DogColour LIKE @dogcolour OR DogName LIKE @dogname OR ApplicationDate LIKE @applicationdate ) AND TagNum > 0GO
I don't know why it is creating links inside my SP -ignore them. TagNum is the primary key, if that makes a difference.
On the webpage, it ONLY works when every field has been filled (and then it will only return 1 row, as it should, given the data entered). Debugging has shown that when nothing is entered it passes "".
Any ideas?
I am trying to retrieve data from two different tables. One of the tables has more than 20 columns some of which are null. I would like to retrieve data from both tables excluding the columns which have null values. How do I do this?
View 3 Replies View RelatedWould this take care of null values in either a.asset or b.asset?
SELECT convert(decimal(15,1),(sum(isnull(a.asset,0))/1000.0)+(sum(isnull(b.asset,0))/1000.0)) as total_assets
What's throwing me off is that there are multiple a.asset or b.asset for each unique ID. It seems to work, but I'm not following the logic too well. If I were doing this in another language, I would loop through, summing a.asset and b.asset wherever it's not null for each unique ID.
Hi,
How can I use "Derived Column" to check if a Datetime value is null or not and if null to insert 00/00/00 instead. ?
The background being that while using a "Derived Column" to change a Column from a (DT_DATE) to a (DT_DBTIMESTAMP) everytime I get a null value it see's it as a error.
And the column in particular has ~ 37 K blank / null fields so Im getting a lot of errors
So far I have tried to use something like
ISNULL([Column 34])
Or
SELECT ISNULL(ID, '00/00/0000') FROM [Column 34]
Or
SELECT ISNULL(au_id, '00/00/0000 00:00') AS ssn
FROM [Column 34
but none seems to work [Column 34] being the offending column.
What a normally use is just a simple "(DT_DBTIMESTAMP)[Column 34]"
in the expression column, which seems to work well, but here I get alot of errors
Any ideas?
I set up a new SQL database file, in that file I allowed nulls, When I went through code to save the record, the exception is saying it doesnt allow nulls.
Before I get to involved with SQL, is it a bad practice to use nulls?
If it is what do you enter in place of the null value,
which will lead to more code, right?
Davids Learning SQL
hi ive got a inert sub where i grab values from text boxes etxthe values are passed to a stored procedure however , one of these fields is a date field , but the field is not required ...so on this line if the date text box is left blank i get an error , not a valid date .Parameters.Add("@actiondate", SqlDbType.DateTime).Value = txtActionDate.Texti have tried ( the actiondate field can take nulls ..)if txtActionDate="" then .Parameters.Add("@actiondate", SqlDbType.DateTime).Value = nothing else.Parameters.Add("@actiondate", SqlDbType.DateTime).Value = txtActionDate.Textend if but this doesnt workwhat is the best way of allowing blank values to be passed to the stored procedure( it doesnt fall over with normal text / varchar fields ) thanks
View 1 Replies View RelatedI have a sproce that accepts null for one of its parameters I can execute the sproce and enter null and it works fine, it returns all rows. When I try doing this with my GridView and the SQLDataSource it does not work. I need some help in understanding how the SQLDatasource wants a null. Here is what the parameter row of the SQLDataSource looks like.
<asp:ControlParameter ControlID="EnteredByText" DefaultValue="Null" Name="EnteredBy" PropertyName="Text"
Type="String" ConvertEmptyStringToNull="true" />
In my sproce I have setup the parameter as follows;
@EnteredBy Nvarchar(50)=Null
In my WHERE Clause I have:
WHERE (tblClient.EnteredBy = @EnteredBy OR @EnteredBy IS NULL)
How can I check on NULL values in a Select statement?
SELECT ID FROM TabelWHERE somecolumn <> NULL??
I wonder if someone out there can help me. I am writing an ASP application to query a MSSQL database. The users will be able to use one or all of 4 columns. There may be time when the columns are empty (null). How can I write a select query to ignore null values? A rough example of what I am talking about it below.
select * from table where value1='something' value2=<null> value3='something' value4=<null>
I would like to ignore the null values so that in effect the statement would just do the following.
select * from table where value1='something' and value3='something'
I realize my syntax is wrong but I think you get the idea. Any thoughts?