1

Task: convert a DateTme column (cast as varchar) that may contain 1753-01-01 to an empty string.

What is the most efficient way to do this in T-SQL?

For example, if I use:

SELECT 
    LEFT(CONVERT(VARCHAR, DateCol1, 120), 10) 
FROM
    ourDatabase.dbo.ourTable

How can I efficiently change any row containing '1753-01-01' to an empty string?

NOTE: it appears the value '1753-01-01' really does appear in the database table; it sometimes is returned where there are nulls. One can even do a datepart yy and get 1753, so it is NOT ‘null’ in the ordinary sense.

Please see also:
What is the significance of 1/1/1753 in SQL Server? for background on this specific date.

Also, please see: {general discussion of Datetime to varchar conversion}:

How to convert DateTime to VarChar

This is for Microsoft SQL Server 2014 using SSMS 14.0.17

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
JosephDoggie
  • 1,514
  • 4
  • 27
  • 57
  • 1
    could you not update the table, converting those dates to your empty string, so that your selects don't have to do any conversion? – Junc Mayl Apr 24 '19 at 20:16
  • That's a good thought, but it is not allowed. – JosephDoggie Apr 24 '19 at 20:20
  • 1
    Aside: From [`VarChar(n)`](https://learn.microsoft.com/en-us/sql/t-sql/data-types/char-and-varchar-transact-sql?view=sql-server-2017): "When _n_ is not specified in a data definition or variable declaration statement, the default length is 1. When _n_ is not specified when using the CAST and CONVERT functions, the default length is 30." The best practice is to _always_ specify a length. – HABO Apr 24 '19 at 20:53

1 Answers1

3

Use replace().

SELECT replace(left(convert(varchar, datecol1, 120), 10), '1753-01-01', '')
       FROM ourtable;

db<>fiddle

sticky bit
  • 36,626
  • 12
  • 31
  • 42
  • Just what the 'doctor' ordered {I happen to be a PhD}. I'd spell replace with a capital 'R' but that's not important. (-: – JosephDoggie Apr 24 '19 at 20:30