SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Bigint dates
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

daouddajani
Starting Member

Israel
1 Posts

Posted - 08/21/2013 :  11:18:48  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3326 Posts

Posted - 08/21/2013 :  11:35:01  Show Profile  Reply with Quote
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

USA
319 Posts

Posted - 08/21/2013 :  13:49:12  Show Profile  Reply with Quote
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

Edited by - lazerath on 08/21/2013 13:55:22
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000