converting bigint to date

_{Friday, July 27, 2012 7:42 PM}

Hello, 

I have a column with bigint values.  They represent dates.  So 19970217 for February 17, 1997.

How can I convert the bigint values to a date format that looks like February 17, 1997?

Thank you, 

/Sean M/

_{Friday, July 27, 2012 7:51 PM}

Try:

SELECT CAST(CAST(CAST(19970217 AS VARCHAR(30)) AS DATETIME) AS VARCHAR(11))

Please mark as answer if this answers your question. Please mark as helpful if you found this post was helpful.

_{Friday, July 27, 2012 7:55 PM}

Hi,

declare @d bigint
select @d =  19970217 

--SQL 2005
select convert(varchar(20),convert(datetime,CONVERT(varchar(10),@d),112),100)

--SQL 2008
select convert(varchar(20),convert(date,CONVERT(varchar(10),@d),112),100)

--Full Month name
select DateName(month,convert(date,CONVERT(varchar(10),@d),112))
     + ' '
     + DateName(day,convert(date,CONVERT(varchar(10),@d),112))
     + ', '
     + DateName(year,convert(date,CONVERT(varchar(10),@d),112))
     

- Chintak (My Blog)

_{Friday, July 27, 2012 7:57 PM}

These both work when setting the int to a single value.  How can I set the int to all the values in a column in one of my tables?  In other words, instead of putting 19970217, can I just pass in a paramter (@something) which is tied to a column?

/Sean M/

_{Friday, July 27, 2012 8:00 PM}

Try:

SELECT CAST(CAST(CAST(ColumnNameHere AS VARCHAR(30)) AS DATETIME) AS VARCHAR(11))
FROM TableNameHere WHERE ColumnNameHere = @Something

Please mark as answer if this answers your question. Please mark as helpful if you found this post was helpful.

_{Friday, July 27, 2012 8:01 PM}

Hi,

See if this the same you want..

declare @t table
(
d bigint
)
insert into @t
select 19970217 

--SQL 2005
select convert(varchar(20),convert(datetime,CONVERT(varchar(10),d),112),100)
from @t
--SQL 2008
select convert(varchar(20),convert(date,CONVERT(varchar(10),d),112),100)
from @t

--Full Month name
select DateName(month,convert(date,CONVERT(varchar(10),d),112))
     + ' '
     + DateName(day,convert(date,CONVERT(varchar(10),d),112))
     + ', '
     + DateName(year,convert(date,CONVERT(varchar(10),d),112))
from @t
     

- Chintak (My Blog)

_{Friday, July 27, 2012 8:12 PM}

Peso's original answer works fine, once you realize that the CTE was producing a logical 'table' that the query selected from.  Chintak made that table more explicit.

One note.  If you are getting data that looks like datetime data but may not have been properly constrained at input time, you may have issues.  See: http://www.karaszi.com/SQLServer/info_datetime.asp

RLF

_{Friday, July 27, 2012 8:14 PM}

Thank you for response.  But how can I get thousands of bigint from a column....and convert them all at once....and put those converted values into a new table?

/Sean M/

_{Friday, July 27, 2012 8:23 PM}

INSERT newtbl(datecol)
   SELECT CASE WHEN isdate(cast(bigintcol AS char(8))) = 1
               THEN cast(bigintcol AS char(8))
          END
   FROM   tbl
   WHERE  isdate(cast(bigintcol AS char(8))) = 1

Some notes here: I assume that the new column has the date or datetime data type, which is the data type you should use for dates. This data type has not a format in itself, nor should it have.

I've added logic to handle invalid dates in the current data, because you can bet your rear parts that such data exists.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

_{Friday, July 27, 2012 8:34 PM}

Erland.... I run this...and I get all the original values into my table.  No conversion has taken place. 

/Sean M/

_{Friday, July 27, 2012 8:40 PM}

Hi JH !

You may get the desired output using below query;

INSERT newtbl(datecol)
   SELECT CASE WHEN isdate(cast(bigintcol AS char(8))) = 1
               THEN cast(cast(bigintcol AS char(8)) AS DATETIME)
          END
   FROM   tbl
   WHERE  isdate(cast(bigintcol AS char(8))) = 1

I think Erland just missed to cast it to DATETIME. Here is the revised version

Please let me know if this helps. Hopefully i have answered you correctly.

Thanks, Hasham

_{Friday, July 27, 2012 8:49 PM}

Now I'm getting the following error: 

Implicit conversion from data type datetime to bigint is not allowed. Use the CONVERT function to run this query.

/Sean M/

which is weird...because I'm trying to go from bigint to datetime....not the other way around!

_{Friday, July 27, 2012 9:15 PM}

Hi JH !

You may get the desired output using below query;

INSERT newtbl(datecol)
SELECT    CASE    WHEN ISDATE(CONVERT(VARCHAR,CAST(bigintcol AS char(8)),112)) = 1
                THEN CAST(CONVERT(VARCHAR,CAST(bigintcol AS char(8)),112) AS DATETIME)
        END
FROM    tbl
WHERE    ISDATE(CONVERT(VARCHAR,CAST(bigintcol AS char(8)),112)) = 1

Here is the revised version, hope this won't break out;

Please let me know if this helps. Hopefully i have answered you correctly.

Thanks, Hasham

_{Friday, July 27, 2012 9:30 PM}

SwePeso.....MVP indeed :) 

This worked beautifully!!! :) 

Thank you everyone for all your help!

Cheers, 

Sean 

/Sean M/

_{Friday, July 27, 2012 10:34 PM}

Erland.... I run this...and I get all the original values into my table.  No conversion has taken place. 

Then you did not read the instructions. The data type for the new column should be date (or datetime if you are on SQL 2005). You should not store the dates in some text format. That's a bad thing to do.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se