Insert zero of 5th place of string

1

Is there an easy way to insert a zero in the 5 place of each date field where men Len is < 8.

The is suppose to be a day field but it’s varchar so some dates only have 7 chars . A data is 8 chars so it’s missing a number
Example 1999230
Should be 19990230

2

Sure, that's easy to do.

But how do you know if it's supposed to be 19990230 or 19991230?

3 
	if OBJECT_ID('tempdb..#dstyles') is not null
		drop table #dstyles


	create table #dstyles(bootleg varchar(50))

	insert into #dstyles
	select distinct top 200  
	case 
	  when DATEPART(mm, dateadd(mm,column_id, getdate())) < 10 
			then cast( year(dateadd(mm,column_id, getdate())) as varchar(4)) + 
			     cast(month(dateadd(mm,column_id, getdate())) as varchar(2)) +
				 cast(day(dateadd(mm,column_id, getdate())) as varchar(2))
	  else convert(varchar(8), getdate(), 112) end as yy
	from sys.all_columns


	select case
	          when len(bootleg) < 8 then Stuff(bootleg, 5, 0, '0')
			  else bootleg end ,
			  bootleg
	  from #dstyles
4

I would be more concerned about 1999111 or similar variations. Is that 19990111 or 19991101?

5

Isn't that the exact same issue I pointed out? That you can't tell with 1999230 whether the final month should be 02 (Feb) or 12 (Dec)?

6

Scott,

it's not the same issue. From what it looks like, the month and day are being treated as integers when combining, so the leading zero is getting cut off of the month or day.

7

It is, but OK, I'll let others decide on this themselves.

8

until @Dstyles chimes in, it is all wild guesses :slight_smile:

9

Not really - the OP stated that there are missing zeroes for the month portion of the string, not a missing 0 or 1 for the month. It is also likely that the construction of the string date would be dropping the leading zero for both month and day - which is going to be even harder to reconcile.

1 Like
10

Ok, that does explain it. Since it's known to be a zero, a (relatively) simple STUFF takes care of it.

UPDATE dbo.table_name
SET date_char = STUFF(date_char, 5, 0, '0')
WHERE LEN(date_char) = 7 AND ISDATE(STUFF(date_char, 5, 0, '0')) = 1
11

Thx guys I got it to work , will post what I did shortly

12

From Stackoverflow:

These are already formatted conversions:
CONVERT(VARCHAR(8), GETDATE(), 112) + REPLACE(CONVERT(varchar, GETDATE(), 108), ':','')
outputs:
20200409 165122