Member Avatar for yanivlt777

Hi,
I have a table with date column where the date data is represented by a number.
I would like to view this data in a date format (dd-mm-yyyy) after running the sql statement.
Appriciate if you could let me what is the code for it.

Thanks.
Y

  • 7 Contributors
  • 11 Replies
  • 794 Views
  • 1 Year Discussion Span
  • Latest Post Latest Post by relalimarmo

Dani AI

Generated

Short primer and a practical checklist for this thread.

The raw values you posted (for example 32143 → 1988-01-01 in your Excel workup) look like serial day counts produced by Excel / OLE-style dates rather than a compact yymmdd string. Excel workbooks can use either the 1900 or 1904 date system, and OLE/COM dates use a slightly different origin — those differences are why a single numeric value can produce different calendar dates depending on how it was created. (support.microsoft.com)

Quick diagnostic you can run (no code shown here so it is easy to apply in Excel or SQL):

  • Gather several known pairs (numeric value + the correct calendar date).
  • Check whether the numbers have a fractional part (time-of-day) or are integers (date only).
  • Try interpreting the number as an Excel serial against the two common epochs (Windows 1900 vs Mac 1904) and the OLE origin; one will match your known pairs. If results are off by a day for dates around 1900, remember the well-known Excel 1900 leap‑year quirk. (learn.microsoft.com)

Conversion guidance and safety:

  • Once you know the epoch, convert by adding the serial-day count to that epoch and validate with several known rows before updating data permanently.
  • Protect conversions with a “try/validate” pass (e.g., TRY_CAST/TRY_CONVERT or equivalent) so bad rows don’t fail the whole job and so you can log mismatches. Always write results into a new DATE/DATETIME column and keep the original numeric column until you’re confident. (learn.microsoft.com)

Notes tied to forum replies: was headed in the right direction about an epoch; ’s DATEADD example shows the off‑by‑one symptom you may see; ’s padding idea is the right pattern when the values are actually six‑digit yymmdd numbers. When in doubt, validate against multiple samples and keep the original values until cleanup is complete.

Recommended Answers

All 11 Replies

Member Avatar for Member #905211

What kind of numeric?

Is it like this (mmddyyy being 07311980)? Please give more detail into how the number represents a date.

Member Avatar for yanivlt777

It's like 12320640 where the actual date after some calculation/manipulation in excel is 5-June-2011.
Any idea?

Member Avatar for Member #905211

Normally if a date is a number they are calculated based off of a starting date. For Excel it is normally January 1 1900 (there is a way to have it change to January 2 1904). I'm not sure where you are getting the number 12320640 from though. Is this real sample data?

Member Avatar for yanivlt777

Yes, this is a real sample.
1-January- 1988 for example is represented by 32143.

Member Avatar for Member #905211

Starting date is (in yyyy-mm-dd format) 1899-12-31, now it's just the number of days since that date (remember leap years).

Member Avatar for Member #46692

I think the point is why this data wasn't stored properly into the database!

I understand most excel readers dump dates out in this format but...

http://www.cpearson.com/excel/datetime.htm

Member Avatar for Member #46692

Also useful...

Member Avatar for crishlay

use convert(datetime, yourdate, 101) for datetime convertion

u can change 101 to 102,103... etc as per your needs

Member Avatar for fazil.tk

Hai,
I have a similar problem... here i have numeric column with values like 120908 (as yymmdd)
How to convert it to date...?
Please help...?

Member Avatar for Eagletalon 
SELECT DATEADD(DAY, 32143, CONVERT(DATE, '1899/12/31', 111)) -- RESULT = 1988-01-02
Member Avatar for relalimarmo

The function call below converts a YYMMDD numeric value to a datetime data type. The second parameter adds leading zeros in case the date has less than 6 digits (ex: 80910 = Sep 10, 2008) then trims it back down to the 6 right-most digits.

declare @i int
set @i = 120908
select convert(
    datetime,
    right('00'+ltrim(str(@i)),6)
)
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.