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

Convert minutes to hours:minutes

Hi all,
I think I have got the stupiest question but, I've got minutes worked out in
a field using dateDiff.... now how do I convert my minutes to hours and
minutes? Only thing I wanna know is if my DateTimeDiff field has got 408
minutes in it, how do I convert it to hours minutes and seconds? It should
still be kept in dates cause I want to sum all the values to give me a total
hours minutes and seconds.
Thanks
RudiRudi - I suggest using the Report Properties "Code" tab and pass in the
minutes and return the hours / minutes time value. This way you can use a
function to calculate when you need it. Just a suggestion. :)
=-Chris
"Rudi Groenewald" <noone@.paflof.com> wrote in message
news:cu7tq0$o5k$1@.ctb-nnrp2.saix.net...
> Hi all,
> I think I have got the stupiest question but, I've got minutes worked out
> in
> a field using dateDiff.... now how do I convert my minutes to hours and
> minutes? Only thing I wanna know is if my DateTimeDiff field has got 408
> minutes in it, how do I convert it to hours minutes and seconds? It should
> still be kept in dates cause I want to sum all the values to give me a
> total
> hours minutes and seconds.
> Thanks
> Rudi
>

convert datetime to age

I have used the following query to get an age from datetime:
select age
from (select case when dateadd(year, datediff(year, birthdate, getdate()),
birthdate) > getdate() then datediff(year, birthdate, getdate())
-1 else datediff(year, birthdate, getdate()) end as age
from userprofile) as age
I'm trying to get two fields from table userprofile (userid, birthdate) and
viewed as
userid and the age.
gone blank on trying to figure out how to implement the previous query with
mulitple fields from a table.Why are you using a subquery?
select userid, birthdate as age case when dateadd(year, datediff(year,
birthdate, getdate()),
birthdate) > getdate() then datediff(year, birthdate, getdate()) -1 else
datediff(year, birthdate, getdate()) end as age
from userprofile
"chad" wrote:

> I have used the following query to get an age from datetime:
> select age
> from (select case when dateadd(year, datediff(year, birthdate, getdate()),
> birthdate) > getdate() then datediff(year, birthdate, getdate())
> -1 else datediff(year, birthdate, getdate()) end as age
> from userprofile) as age
> I'm trying to get two fields from table userprofile (userid, birthdate) an
d
> viewed as
> userid and the age.
> gone blank on trying to figure out how to implement the previous query wit
h
> mulitple fields from a table.|||Select DateDiff(yy, BirthDate, @.TargetDate)
- Case
When Cast(
Cast(DatePart(yyyy, BirthDate) As Char(4))
+ Right('00' + Cast(DatePart(mm, @.TargetDate) As
VarChar(2)), 2)
+ Right('00' + Cast(DatePart(dd, @.TargetDate) As
VarChar(2)), 2) As SmallDateTime)
< @.TargetDate
Then 1
Else 0
End
From Table
Where @.TargetDate is the date you are comparing against. You could replace
this with GetDate() if you simply wanted their age as of now.
The second part creates an ISO date (yyyymmdd) in the year of the target
date for the month and day of the birth date. If the birth day in the target
year has not transpired, it subtracts one from the total. This technique is
impervious to leap years (well, as impervious as SQL's date interpretation)
and ensures that the value that comes out is one that a human would
interpret as the correct age.
Thomas
"chad" <chad@.discussions.microsoft.com> wrote in message
news:9048E685-D96B-40F4-9B6C-7C6D56D859E4@.microsoft.com...
>I have used the following query to get an age from datetime:
> select age
> from (select case when dateadd(year, datediff(year, birthdate, getdate()),
> birthdate) > getdate() then datediff(year, birthdate, getdate())
> -1 else datediff(year, birthdate, getdate()) end as age
> from userprofile) as age
> I'm trying to get two fields from table userprofile (userid, birthdate)
> and
> viewed as
> userid and the age.
> gone blank on trying to figure out how to implement the previous query
> with
> mulitple fields from a table.|||the original query was for age conversion only and it worked well.
there was syntax errors in your query, where's the THEN expression.
appreciate the help.
"HP" wrote:
> Why are you using a subquery?
> select userid, birthdate as age case when dateadd(year, datediff(year,
> birthdate, getdate()),
> birthdate) > getdate() then datediff(year, birthdate, getdate()) -1 else
> datediff(year, birthdate, getdate()) end as age
> from userprofile
>
> "chad" wrote:
>|||errors out at row 2893 out of 9998.
The conversion of char data type to smalldatetime data type resulted in an
out-of-range smalldatetime value.
"Thomas C" wrote:

> Select DateDiff(yy, BirthDate, @.TargetDate)
> - Case
> When Cast(
> Cast(DatePart(yyyy, BirthDate) As Char(4))
> + Right('00' + Cast(DatePart(mm, @.TargetDate) As
> VarChar(2)), 2)
> + Right('00' + Cast(DatePart(dd, @.TargetDate) As
> VarChar(2)), 2) As SmallDateTime)
> < @.TargetDate
> Then 1
> Else 0
> End
> From Table
> Where @.TargetDate is the date you are comparing against. You could replace
> this with GetDate() if you simply wanted their age as of now.
> The second part creates an ISO date (yyyymmdd) in the year of the target
> date for the month and day of the birth date. If the birth day in the targ
et
> year has not transpired, it subtracts one from the total. This technique i
s
> impervious to leap years (well, as impervious as SQL's date interpretation
)
> and ensures that the value that comes out is one that a human would
> interpret as the correct age.
>
> Thomas
>
> "chad" <chad@.discussions.microsoft.com> wrote in message
> news:9048E685-D96B-40F4-9B6C-7C6D56D859E4@.microsoft.com...
>
>|||Figured it out:
SELECT Age=datediff(year,Birthdate,getdate()),
userid
FROM UserProfile
WHERE UserProfile.Birthdate IS NOT NULL AND
datediff(year,Birthdate,getdate())>=0
ORDER BY userid
"chad" wrote:

> I have used the following query to get an age from datetime:
> select age
> from (select case when dateadd(year, datediff(year, birthdate, getdate()),
> birthdate) > getdate() then datediff(year, birthdate, getdate())
> -1 else datediff(year, birthdate, getdate()) end as age
> from userprofile) as age
> I'm trying to get two fields from table userprofile (userid, birthdate) an
d
> viewed as
> userid and the age.
> gone blank on trying to figure out how to implement the previous query wit
h
> mulitple fields from a table.