SQLite Forum

strange strftime returns at julian day zero
Login
Nothing.

```
sqlite> select strftime('%Y-%m-%d %H:%M:%f',0), strftime('%J','-4713-11-24 12:00:00.000');
┌─────────────────────────────────┬───────────────────────────────────────────┐
│ strftime('%Y-%m-%d %H:%M:%f',0) │ strftime('%J','-4713-11-24 12:00:00.000') │
├─────────────────────────────────┼───────────────────────────────────────────┤
│ -471-11-24 12:00:00.000         │ 0                                         │
└─────────────────────────────────┴───────────────────────────────────────────┘
```

The %Y format specifier returns the leftmost 4 characters of the output format "%04d".  Since the actual year value is -4713 CE, which will not, with the sign, fit in 4 characters, it is truncated on the right to 4 characters, or "-471".  The input parser does not, however, have this problem, but does require that there be 4 digits in the year with an optional leading - sign.  

Dates prior to January 1st, 1 BCE ('0000-01-01 00:00:00.000') cannot "round trip" the parser.

Note that the units for %Y is "year CE", and the - sign does not mean BCE.  Everything before 1 CE is "off by one" since year 0 does not exist (that is 0 CE is 1 BCE).