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?
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -