Bigint dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Bigint dates

Author

Topic

daouddajani
Starting Member

1 Post

Posted - 2013-08-21 : 11:18:48

i have a client database where customers birthdays are stored as bigint. i need to create an sql statement that takes today date and return all the customers whom birthday matches today.

how can i do that ?!!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-21 : 11:35:01

quote:
Originally posted by daouddajani

i have a client database where customers birthdays are stored as bigint. i need to create an sql statement that takes today date and return all the customers whom birthday matches today.

how can i do that ?!!

How is the date represented in the bigint column? For example, what would be entered into the database if the birth date is today, August 21, 2013?

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2013-08-21 : 13:49:12

Assuming it's 0 based, here's a walkthrough that includes the setup of sample data. You can run the whole thing to play around or just skip to the last statement to see the expression you need.


----------------------------------------------------------------------------------
-- Get some numbers to play with, in this case, 64
----------------------------------------------------------------------------------
;WITH E1(N) AS(SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1)
,E2(N) AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as N FROM E1 a, E1 b, E1 c)

----------------------------------------------------------------------------------
-- Using 1-64, calculate a BIGINT from 0 (1/1/1900) to generate a starting dataset
----------------------------------------------------------------------------------

, cte_BIGINT_BDays
AS
(
select N,convert(bigint,dateadd(d,datediff(d,0,getdate())+N-1,0)) as BIGINT_BDay
from E2
)
--SELECT * FROM cte_BIGINT_BDays

/*--------------------------------------------------------------------------------
-- Results
----------------------------------------------------------------------------------
BIGINT_BDay
N	BIGINT_BDay
1	41505
2	41506
... other entries ...
63	41567
64	41568

(64 row(s) affected)
--*/ -----------------------------------------------------------------------------

----------------------------------------------------------------------------------
-- Now show how to calculate the DATETIME and VARCHAR representations from the BIGINT
----------------------------------------------------------------------------------

SELECT N,
DATEADD(d,BIGINT_BDay,0) as DATETIME_BDay,
CONVERT(VARCHAR(10),DATEADD(d,BIGINT_BDay,0),101) AS VARCHAR_BDay
FROM cte_BIGINT_BDays

/*-------------------------------------------------------------------
-- Results
---------------------------------------------------------------------
N	DATETIME_BDay	VARCHAR_BDay
1	2013-08-21 00:00:00.000	08/21/2013
2	2013-08-22 00:00:00.000	08/22/2013
... other entries ...
63	2013-10-22 00:00:00.000	10/22/2013
64	2013-10-23 00:00:00.000	10/23/2013

(64 row(s) affected)
--*/ ----------------------------------------------------------------

Please note that you will have to determine what the BIGINT is based on.

* Edit: Added more comments and cleaned up formating

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources