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
Should be 19990230
Sure, that's easy to do.
But how do you know if it's supposed to be 19990230 or 19991230?
if OBJECT_ID('tempdb..#dstyles') is not null
drop table #dstyles
create table #dstyles(bootleg varchar(50))
insert into #dstyles
select distinct top 200
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
when len(bootleg) < 8 then Stuff(bootleg, 5, 0, '0')
else bootleg end ,
I would be more concerned about 1999111 or similar variations. Is that 19990111 or 19991101?
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)?
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.
It is, but OK, I'll let others decide on this themselves.
until @Dstyles chimes in, it is all wild guesses
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.
Ok, that does explain it. Since it's known to be a zero, a (relatively) simple STUFF takes care of it.
SET date_char = STUFF(date_char, 5, 0, '0')
WHERE LEN(date_char) = 7 AND ISDATE(STUFF(date_char, 5, 0, '0')) = 1
Thx guys I got it to work , will post what I did shortly
These are already formatted conversions:
CONVERT(VARCHAR(8), GETDATE(), 112) + REPLACE(CONVERT(varchar, GETDATE(), 108), ':','')