Converting empty string to Null when inserting/updating

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 areNULL 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 NULL
before the values are inserted.

Thanks guys.

You REALLY should store your dates as a datetime. There is no localization "Problem" with datetimes if you use them correctly, and you can't sort and/or generate good indexes if they are stored in a nvarchar field (Unless you specifically use the YYYYMMDD or YYYY-MM-DD format).

That aside, yes, try NULLIF() like:

INSERT INTO MyTable(col1) VALUES (NULLIF(@.val1,''))

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_nos-nz_3uhy.asp

Converting Access report to Reporting Services

Hi,

I am trying to convert an Access report where I use expressions to calculate the number of males/females for example.

I have used the following expression, but it always returns an incorrect figure:

=Sum(IIf(Fields!gender.Value ="Female", 1, 0))

When I check the query however it has 46 rows = to 'Female', but when I previes the report it always comes out as 24.

What's causing this?

Thanks

Hi,

From your description, it seems the expression doesn't return the right number of those 'Female' field, right?

What I can see is to use a TRIM function to remove all the leading and trailing while-spaces characters from the value in each filed. Sometimes theses leading and trailing white-spaces characters may cause the expression "IIf(Fields!gender.Value = "Female", 1, 0)" returns 0. You may use TRIM to have a try, see the following code:

=Sum(IIf(TRIM(Fields!gender.Value) = "Female", 1, 0))

Thanks.

|||

Thanks, that seems to have worked.
I wasn't aware of the leading and trailing white-spaces.

Convert Oracle 8.1.7 database to SQL 2005 sizing estimates

We are planning on converting a Oracle 8.1.7 database to SQL 2005 Standard Edition. How can we calculate the disk space requirements needed to convert the database. Any help is appreciated.

TIA,

Michelle

If it's built and indexed the same way, it will probably be very close to the size of the original Oracle database.

-Ryan / Kardax

Convert Numbers

Hi there, I'm using a SQL 2000 and I don't know how to convert (or calculate)some data as described below:

Examples
746585 means 7 hours, 46 minutes and 58.5 seconds
038335 means 38 minutes and 33.5 seconds
005215 means 5 minutes and 21.5 seconds
000455 means 45.5 seconds
000070 means 7 seconds
000065 means 6.5 seconds

How do I convert:
746585 in 466.975 minutes
038335 in 38.558 minutes
005215 in 5.358 minutes
000455 in 0.758 minutes
000070 in 0.166 minutes
000065 in 0.1083 minutes

Could anyone help me?

Thanks in advance,

AndreOkay, you have a string with numbers of the following meaning:
hmmssc

To convert it into a datetime value:

declare @.s as char(6)
set @.s='746585'

select convert(datetime,'0'+substring(@.s,1,1)+':'+
substring(@.s,2,2)+':'+substring(@.s,4,2)+'.'+
substring(@.s,6,1), 14)
go

To compute the minutes:

select cast(substring(@.s,1,1) as int)*60+
cast(substring(@.s,2,2) as int)+ cast(substring(@.s,4,3) as dec(10,5))/600

Cheers! :D|||Thank you for your reply DoktorBlue you hit almost there!!! Sorry, I just started with SQL Server...

I tryed that string:
declare @.s as char(6)
set @.s='746585'

select convert(datetime,'0'+substring(@.s,1,1)+':'+
substring(@.s,2,2)+':'+substring(@.s,4,2)+'.'+
substring(@.s,6,1), 14)
go

select cast(substring(@.s,1,1) as int)*60+
cast(substring(@.s,2,2) as int)+ cast(substring(@.s,4,3) as dec(10,5))/600

Then I got the result:
1900-01-01 07:46:58.500

The time 07:46:58.500 is very correct but it should mean a period of time, not a date.

There is a file available at http://www.aga.cc/download/sample.txt (the original one has over than 200 thousand records), so I need to calculate the total of each access#'s time and cost, something like this:

select access#, sum (time) as TIME, sum (cost) AS PRICE, sum (PRICE) / sum (TIME) as MONEY from sample group by access#.

Thank you again for your cooperation.

Andre Mori|||Originally posted by mori
select access#, sum (time) as TIME, sum (cost) AS PRICE, sum (PRICE) / sum (TIME) as MONEY from sample group by access#.


Hi Andre,

You want to have sum(time). In minutes? Use the second transformation.

You want to display your time as a string? Consider to write something like

select substring(@.s,1,1) + ' hours, ' +
substring(@.s,2,2) + ' minutes, ' + substring(@.s,4,2) +
'.' + substring(@.s,6,1) + ' seconds'|||Great DoktorBlue, 100530 converted into 60.883333333, worked fine!!! Thank you very much.

I have another awful question, how do I set @.s='the_collumn_i_want_to_convert'?

Once again, thanks.|||I used a variable to show the principle. You have to replace @.s by your field name.|||Hi DoktorBlue, please don't get angry OK?

I tryed:

declare @.time as char(6)
set @.Time = Time

select cast(substring(@.Time,1,1) as int)*60+cast(substring(@.Time,2,2) as int)+ cast(substring(@.Time,4,3) as dec(10,5))/600

Results:
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'Time'.

declare @.s as char(6)
set @.s=TIME

select cast(substring(@.s,1,1) as int)*60+cast(substring(@.s,2,2) as int)+ cast(substring(@.s,4,3) as dec(10,5))/600

Results:
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'TIME'.

I wasn't able to make it work.....

Thanks...|||No, no, no. I assume that you have a table T with a field TIME. All you have to do is to say

SELECT TIME, cast(substring(Time,1,1) as int)*60+cast(substring(Time,2,2) as int)+ cast(substring(Time,4,3) as dec(10,5))/600
FROM T|||Hi DoktorBlue, I know you are tired of me, right? :>(

Tryed:
SELECT TIME, cast(substring(Time,1,1) as int)*60+cast(substring(Time,2,2) as int)+ cast(substring(Time,4,3) as dec(10,5))/600
FROM sample2

Returned:
Server: Msg 256, Level 16, State 1, Line 1
The data type int is invalid for the substring function. Allowed types are: char/varchar, nchar/nvarchar, and binary/varbinary.
Server: Msg 256, Level 16, State 1, Line 1
The data type int is invalid for the substring function. Allowed types are: char/varchar, nchar/nvarchar, and binary/varbinary.
Server: Msg 256, Level 16, State 1, Line 1
The data type int is invalid for the substring function. Allowed types are: char/varchar, nchar/nvarchar, and binary/varbinary.

sample2 table has the fields:
# VARCHAR 255
Access# VARCHAR 255
Time INT
Cost INT

Do I need to declare?

Thanks again.

Thanks.|||Hi Andre,

Slowly, but we getting somewhere ....

Your error message indicates, that your TIME field isn't a CHAR, but an INT. So, replace all occurences of

substring(Time,

by

substring(right('000000' + cast(TIME as VARCHAR(6)), 6),|||Hi there, I got this code from another colleague that worked fine:

SELECT access#,
CAST(SUM(((CAST(SUBSTRING(CAST(time AS CHAR(7)), LEN(time)-2, 3) AS DECIMAL)/10)/60) +
CAST(SUBSTRING(CAST(time AS CHAR(7)), LEN(time)-4, 2) AS INT) +
(CAST(SUBSTRING(CAST(time AS CHAR(7)), LEN(time)-6, 2) AS INT)*60)) AS DECIMAL(8,2)) AS TIME,
SUM(cost) AS PRICE,
CAST((SUM(cost) / SUM(((CAST(SUBSTRING(CAST(time AS CHAR(7)), LEN(time)-2, 3) AS DECIMAL)/10)/60) +
CAST(SUBSTRING(CAST(time AS CHAR(7)), LEN(time)-4, 2) AS INT) +
(CAST(SUBSTRING(CAST(time AS CHAR(7)), LEN(time)-6, 2) AS INT)*60))) AS DECIMAL(8,2)) AS MONEY
FROM sample
GROUP BY access#

Thank you all!

Andre Mori