The only truly safe formats for DATETIME/SMALLDATETIME in SQL Server are:
yyyyMMdd
yyyyMMdd hh:nn:ss[.mmmmmmm]
yyyy-MM-ddThh:nn:ss[.mmmmmmm]
----------^ yes, that T is important!
Anything else is subject to incorrect interpretation by SQL Server, Windows, the provider, the application code, end users, etc. For example, the following always breaks:*
SET LANGUAGE FRENCH;
SELECT CONVERT(DATETIME, '2013-11-13');
Result:
Le paramètre de langue est passé à Français.
Msg 242, Level 16, State 3, Line 2
La conversion d'un type de données varchar en type de données datetime a créé une valeur hors limites.
Just changing the language (which any of your user sessions can do) forced SQL Server to interpret that as YYYY-DD-MM
instead of YYYY-MM-DD
. Similar things can happen with setting like DATEFORMAT
. But these settings are literally ignored when using the above two formats.
Always, always, always use one of the above two formats. If you are passing a variable as a string, stop doing that. If you can't, check to make sure it passes ISDATE()
first. If you are letting people type any date string into a form field, stop doing that, too. Use a date-picker or calendar control and dictate the format of the string before you pass it to SQL Server. Well, depending on the language, just keep it as a datetime value and don't convert it to a string at all.
Please read this post:
There is an exception: SELECT CONVERT(DATE, 'yyyy-mm-dd');
will not break. But I err on the side of consistency rather than using a format only in the one place where I know it doesn't break, and having to use a safer format everywhere else.
Use partition by
on the name
column, and count the rows based on the order of id
-s. Where the row number of the actual row is greater than 1, concatenate id
and name
, else just use the name
.
with t as (
select 1 as id, 'john' as name from dual union all
select 2, 'david' from dual union all
select 3, 'zed' from dual union all
select 4, 'john' from dual union all
select 5, 'david' from dual union all
select 6, 'john' from dual union all
select 7, 'david' from dual union all
select 8, 'john' from dual union all
select 9, 'john' from dual
)
select
id,
case
when row_number() over (partition by name order by id) > 1 then id || ' ' || name
else name
end as name
from t order by id;
ID NAME
---------- ----------------------------------------------
1 john
2 david
3 zed
4 4 john
5 5 david
6 6 john
7 7 david
8 8 john
9 9 john
9 rows selected.
To treat john
, JOHN
, joHN
, etc. equally, simply change the partition by
column, e.g upper(name)
.
select
id,
case
when row_number() over (partition by upper(name) order by id) > 1 then id || ' ' || name
else name
end as name
from t order by id;
Best Answer
Datetime without punctuation and up to 7 second decimals are 21 digits so there would be 1 character (32 - 10 - 21) still missing.
You can use the following to format the way you want.
If
PolicyNumber
is alreadyNVARCHAR
then there is no need to do an explicitCONVERT
. Notice that I hard-coded the last zero.