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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Bigint dates
 Reply to Topic
 Printer Friendly
Author  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

3873 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
343 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

porcrim
Starting Member

13 Posts

Posted - 04/22/2018 :  03:25:41  Show Profile  Reply with Quote
[b][url=http://www.replicwatches.com/]best swiss replica watches[/url][/b][b][url=http://www.replicwatches.com/]best replica watches[/url][/b]<ul><li><strong><a href="http://www.replicwatches.com/">swiss Mechanical movement replica watches</a></strong></li><li><strong><a href="http://www.replicwatches.com/">watches price</a></strong></li><li><strong><a href="http://www.replicwatches.com/">best replica watches</a></strong></li></ul><br><title>Emporio Armani Replica Watch Ss Case Black Dial Stick Marking [aa70] - $232.00 : Professional replica watches stores, replicwatches.com</title><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><meta name="keywords" content="Emporio Armani Replica Watch Ss Case Black Dial Stick Marking [aa70] Replica Audemars Piguet Replica Bell&Ross Watches Replica Emporio Armani Watches Replica Hublot Watches Replica Longines Watches Replica Omega Watches Replica Patek Philippe Watches Replica Rado Watches Replica Rolex Watches Replica Tag Heuer Watches Replica U-Boat Watches Replica Breitling Watches cheap replica watches online sales" /><meta name="description" content="Professional replica watches stores Emporio Armani Replica Watch Ss Case Black Dial Stick Marking [aa70] - Welcome to replica watches outlet stores, the site for all your replica watches needs. The internet is full of vendors and sites trying to sell you replica watches and it isn't always easy finding the most reliable sites. We guarantee the best services with the best replica watches online. replica " /><meta http-equiv="imagetoolbar" content="no" /><link rel="canonical" href="http://www.replicwatches.com/emporio-armani-replica-watch-ss-case-black-dial-stick-marking-aa70-p-272.html" /><link rel="stylesheet" type="text/css" href="http://www.replicwatches.com/includes/templates/polo/css/style_imagehover.css" /><link rel="stylesheet" type="text/css" href="http://www.replicwatches.com/includes/templates/polo/css/stylesheet.css" /><link rel="stylesheet" type="text/css" href="http://www.replicwatches.com/includes/templates/polo/css/stylesheet_css_buttons.css" /><link rel="stylesheet" type="text/css" media="print" href="http://www.replicwatches.com/includes/templates/polo/css/print_stylesheet.css" /><select name="currency" onchange="this.form.submit();"> <option value="USD" selected="selected">US Dollar</option> <option value="EUR">Euro</option> <option value="GBP">GB Pound</option> <option value="CAD">Canadian Dollar</option> <option value="AUD">Australian Dollar</option> <option value="JPY">Jappen Yen</option> <option value="NOK">Norske Krone</option> <option value="SEK">Swedish Krone</option> <option value="DKK">Danish Krone</option> <option value="CNY">CNY</option></select><input type="hidden" name="main_page" value="product_info" /><input type="hidden" name="products_id" value="272" /></form></div></div><div class="leftBoxContainer" id="categories" style="width: 220px"><div class="sidebox-header-left main-sidebox-header-left"><h3 class="leftBoxHeading main-sidebox-header-right" id="categoriesHeading">Categories</h3></div><div id="categoriesContent" class="sideBoxContent"><div class="categories-top-list no-dots"><a class="category-top" href="http://www.replicwatches.com/replica-rado-watches-c-41.html">Replica Rado Watches</a></div><div class="categories-top-list "><a class="category-top" href="http://www.replicwatches.com/replica-hublot-watches-c-5.html">Replica Hublot Watches</a></div><div class="categories-top-list "><a class="category-top" href="http://www.replicwatches.com/replica-audemars-piguet-c-2.html">Replica Audemars Piguet</a></div><div class="categories-top-list "><a class="category-top" href="http://www.replicwatches.com/replica-bellross-watches-c-3.html">Replica Bell&Ross Watches</a></div><div class="categories-top-list "><a class="category-top" href="http://www.replicwatches.com/replica-breitling-watches-c-123.html">Replica Breitling Watches</a></div><div class="categories-top-list "><a class="category-top" href="http://www.replicwatches.com/replica-emporio-armani-watches-c-4.html"><span class="category-subs-selected">Replica Emporio Armani Watches</span></a></div><div class="categories-top-list "><a class="category-top" href="http://www.replicwatches.com/replica-longines-watches-c-18.html">Replica Longines Watches</a></div><div class="categories-top-list "><a class="category-top" href="http://www.replicwatches.com/replica-omega-watches-c-24.html">Replica Omega Watches</a></div><div class="categories-top-list "><a class="category-top" href="http://www.replicwatches.com/replica-patek-philippe-watches-c-35.html">Replica Patek Philippe Watches</a></div><div class="categories-top-list "><a class="category-top" href="http://www.replicwatches.com/replica-rolex-watches-c-44.html">Replica Rolex Watches</a></div><div class="categories-top-list "><a class="category-top" href="http://www.replicwatches.com/replica-tag-heuer-watches-c-59.html">Replica Tag Heuer Watches</a></div><div class="categories-top-list "><a class="category-top" href="http://www.replicwatches.com/replica-uboat-watches-c-67.html">Replica U-Boat Watches</a></div></div></div><div class="leftBoxContainer" id="featured" style="width: 220px"><div class="sidebox-header-left "><h3 class="leftBoxHeading " id="featuredHeading">Featured - <a href="http://www.replicwatches.com/featured_products.html">  [more]</a></h3></div><div class="sideBoxContent centeredContent"><a href="http://www.replicwatches.com/omega-speedmaster-replica-watch-chronograph-automatic-red-dial-22ff-p-1037.html"><img src="http://www.replicwatches.com/images/_small//watches_22/Omega-Watches/Omega-Speedmaster/Omega-Speedmaster-Replica-Watch-Chronograph-170.jpg" alt="Omega Speedmaster Replica Watch Chronograph Automatic Red Dial [22ff]" title=" Omega Speedmaster Replica Watch Chronograph Automatic Red Dial [22ff] " width="130" height="98" /></a><a class="sidebox-products" href="http://www.replicwatches.com/omega-speedmaster-replica-watch-chronograph-automatic-red-dial-22ff-p-1037.html">Omega Speedmaster Replica Watch Chronograph Automatic Red Dial [22ff]</a><div><span class="normalprice">$278.00 </span> <span class="productSpecialPrice">$227.00</span><span class="productPriceDiscount"><br />Save: 18% off</span></div></div><div class="sideBoxContent centeredContent"><a href="http://www.replicwatches.com/omega-speedmaster-replica-watch-chronograph-automatic-white-dial-olympic-edition-ea0c-p-1038.html"><img src="http://www.replicwatches.com/images/_small//watches_22/Omega-Watches/Omega-Speedmaster/Omega-Speedmaster-Replica-Watch-Chronograph-178.jpg" alt="Omega Speedmaster Replica Watch Chronograph Automatic White Dial Olympic Edition [ea0c]" title=" Omega Speedmaster Replica Watch Chronograph Automatic White Dial Olympic Edition [ea0c] " width="130" height="98" /></a><a class="sidebox-products" href="http://www.replicwatches.com/omega-speedmaster-replica-watch-chronograph-automatic-white-dial-olympic-edition-ea0c-p-1038.html">Omega Speedmaster Replica Watch Chronograph Automatic White Dial Olympic Edition [ea0c]</a><div><span class="normalprice">$281.00 </span> <span class="productSpecialPrice">$226.00</span><span class="productPriceDiscount"><br />Save: 20% off</span></div></div><div class="sideBoxContent centeredContent"><a href="http://www.replicwatches.com/omega-speedmaster-replica-watch-co-axial-working-chronograph-white-dial-861f-p-1039.html"><img src="http://www.replicwatches.com/images/_small//watches_22/Omega-Watches/Omega-Speedmaster/Omega-Speedmaster-Replica-Watch-Co-Axial-Working.jpg" alt="Omega Speedmaster Replica Watch Co Axial Working Chronograph White Dial [861f]" title=" Omega Speedmaster Replica Watch Co Axial Working Chronograph White Dial [861f] " width="130" height="98" /></a><a class="sidebox-products" href="http://www.replicwatches.com/omega-speedmaster-replica-watch-co-axial-working-chronograph-white-dial-861f-p-1039.html">Omega Speedmaster Replica Watch Co Axial Working Chronograph White Dial [861f]</a><div><span class="normalprice">$269.00 </span> <span class="productSpecialPrice">$221.00</span><span class="productPriceDiscount"><br />Save: 18% off</span></div></div></div></div></td> <td id="columnCenter" valign="top"> <div id="navBreadCrumb"> <a href="http://www.replicwatches.com/">Home</a> ::  <a href="http://www.replicwatches.com/replica-emporio-armani-watches-c-4.html">Replica Emporio Armani Watches</a> :: Emporio Armani Replica Watch Ss Case Black Dial Stick Marking [aa70]</div><div class="centerColumn" id="productGeneral"><form name="cart_quantity" action="http://www.replicwatches.com/emporio-armani-replica-watch-ss-case-black-dial-stick-marking-aa70-p-272.html?action=add_product" method="post" enctype="multipart/form-data"><div style="float:left; width:350px;"><link rel="stylesheet" href="http://www.replicwatches.com/style/jqzoom.css" type="text/css" media="screen" /><link rel="stylesheet" href="http://www.replicwatches.com/style/jqzoomimages.css" type="text/css" media="screen" /><style type="text/css">.jqzoom{float:left;position:relative;padding:0px;cursor:pointer;width:301px;height:300px;}</style><div id="productMainImage" class="centeredContent back"><div class="jqzoom" > <a href="http://www.replicwatches.com/emporio-armani-replica-watch-ss-case-black-dial-stick-marking-aa70-p-272.html" ><img src="http://www.replicwatches.com/images//watches_22/Emporio-Armani/Emporio-Armani-Replica-Watch-Ss-Case-Black-Dial.jpg" alt="Emporio Armani Replica Watch Ss Case Black Dial Stick Marking [aa70]" jqimg="images//watches_22/Emporio-Armani/Emporio-Armani-Replica-Watch-Ss-Case-Black-Dial.jpg" id="jqzoomimg"></a></div><div style="clear:both;"></div><div id='jqzoomimages' class="smallimages"></div></div></div><div style="width:260px; float:left; margin-left:30px; margin-top:15px;" id='pb-left-column'><div style="font-weight:bold; padding-bottom:10px; font-size:14px;">Emporio Armani Replica Watch Ss Case Black Dial Stick Marking [aa70]</div><span id="productPrices" class="productGeneral"><span class="normalprice">$285.00 </span> <span class="productSpecialPrice">$232.00</span><span class="productPriceDiscount"><br />Save: 19% off</span></span> <div id="cartAdd"> Add to Cart: <input type="text" name="cart_quantity" value="1" maxlength="6" size="4" /><br /><br /><input type="hidden" name="products_id" value="272" /><input type="image" src="http://www.replicwatches.com/includes/templates/polo/buttons/english/button_in_cart.gif" alt="Add to Cart" title=" Add to Cart " /> </div> <br class="clearBoth" /></div><br class="clearBoth" /> <div id="productDescription" class="productGeneral biggerText"><div class="tabTitles"><ul> <li> <h4 tid="t1" class="cur"><strong class=""><span>Description</span></strong></h4> </li></ul></div><p>Welcome to replica watches outlet stores, the site for all your replica watches needs. The internet is full of vendors and sites trying to sell you replica watches and it isn't always easy finding the most reliable sites. We guarantee the best services with the best replica watches online. replica watches are everywhere, and it's important that you're getting the best available on the market today. </p></br><p>Emporio Armani is a renouned code for character clarity as good as conform which it shows in his operation of conform watches which have been dedicated to both duty as good as form. Emporio Armani has combined Fashion watches which not usually demeanour fanciful though additionally reason a pressured as good as a need of day-to-day reason up so a single can find both infrequent as good as sophistication.</p> <p></p> <p>Top quality Japanese Quartz Movement <br />Solid 316 Stainless Steel Case <br />High quality genuine Leather Strap <br />Mineral crystal scratch durable glass face <br />Water-Resistant Man Size: 41mm</p></div><br class="clearBoth" /><div id="img_bg" align="center"><p style='text-align:center;'><a target="_blank" href="http://www.replicwatches.com/images//watches_22/Emporio-Armani/Emporio-Armani-Replica-Watch-Ss-Case-Black-Dial.jpg"><img itemprop="image" src="http://www.replicwatches.com/images//watches_22/Emporio-Armani/Emporio-Armani-Replica-Watch-Ss-Case-Black-Dial.jpg" width=700px alt="/watches_22/Emporio-Armani/Emporio-Armani-Replica-Watch-Ss-Case-Black-Dial.jpg"/></a></p><p style='text-align:center;'><a target="_blank" href="http://www.replicwatches.com/images//watches_22/Emporio-Armani/Emporio-Armani-Replica-Watch-Ss-Case-Black-Dial-1.jpg"><img itemprop="image" src="http://www.replicwatches.com/images//watches_22/Emporio-Armani/Emporio-Armani-Replica-Watch-Ss-Case-Black-Dial-1.jpg" width=700px alt="/watches_22/Emporio-Armani/Emporio-Armani-Replica-Watch-Ss-Case-Black-Dial-1.jpg"/></a></p><p style='text-align:center;'><a target="_blank" href="http://www.replicwatches.com/images//watches_22/Emporio-Armani/Emporio-Armani-Replica-Watch-Ss-Case-Black-Dial-2.jpg"><img itemprop="image" src="http://www.replicwatches.com/images//watches_22/Emporio-Armani/Emporio-Armani-Replica-Watch-Ss-Case-Black-Dial-2.jpg" width=700px alt="/watches_22/Emporio-Armani/Emporio-Armani-Replica-Watch-Ss-Case-Black-Dial-2.jpg"/></a></p><p style='text-align:center;'><a target="_blank" href="http://www.replicwatches.com/images//watches_22/Emporio-Armani/Emporio-Armani-Replica-Watch-Ss-Case-Black-Dial-3.jpg"><img itemprop="image" src="http://www.replicwatches.com/images//watches_22/Emporio-Armani/Emporio-Armani-Replica-Watch-Ss-Case-Black-Dial-3.jpg" width=700px alt="/watches_22/Emporio-Armani/Emporio-Armani-Replica-Watch-Ss-Case-Black-Dial-3.jpg"/></a></p></div><div class="centerBoxWrapper" id="similar_product"><h2 class="centerBoxHeading">Related Products</h2><table><tr><td style="display:block;float:left;width:24.5%;"><div style="width:160px;height:200px;"><a href="http://www.replicwatches.com/emporio-armani-replica-watch-working-chronograph-black-dial-9b31-p-279.html"><img src="http://www.replicwatches.com/images/_small//watches_22/Emporio-Armani/Emporio-Armani-Replica-Watch-Working-Chronograph.jpg" alt="Emporio Armani Replica Watch Working Chronograph Black Dial [9b31]" title=" Emporio Armani Replica Watch Working Chronograph Black Dial [9b31] " width="160" height="120" /></a></div><a href="http://www.replicwatches.com/emporio-armani-replica-watch-working-chronograph-black-dial-9b31-p-279.html">Emporio Armani Replica Watch Working Chronograph Black Dial [9b31]</a></td><td style="display:block;float:left;width:24.5%;"><div style="width:160px;height:200px;"><a href="http://www.replicwatches.com/emporio-armani-replica-watch-classic-black-dial-stick-marking-d9a4-p-234.html"><img src="http://www.replicwatches.com/images/_small//watches_22/Emporio-Armani/Emporio-Armani-Replica-Watch-Classic-Black-Dial-16.jpg" alt="Emporio Armani Replica Watch Classic Black Dial Stick Marking [d9a4]" title=" Emporio Armani Replica Watch Classic Black Dial Stick Marking [d9a4] " width="160" height="120" /></a></div><a href="http://www.replicwatches.com/emporio-armani-replica-watch-classic-black-dial-stick-marking-d9a4-p-234.html">Emporio Armani Replica Watch Classic Black Dial Stick Marking [d9a4]</a></td><td style="display:block;float:left;width:24.5%;"><div style="width:160px;height:200px;"><a href="http://www.replicwatches.com/emporio-armani-replica-watch-working-chronograph-brown-dial-faf5-p-284.html"><img src="http://www.replicwatches.com/images/_small//watches_22/Emporio-Armani/Emporio-Armani-Replica-Watch-Working-Chronograph-40.jpg" alt="Emporio Armani Replica Watch Working Chronograph Brown Dial [faf5]" title=" Emporio Armani Replica Watch Working Chronograph Brown Dial [faf5] " width="160" height="120" /></a></div><a href="http://www.replicwatches.com/emporio-armani-replica-watch-working-chronograph-brown-dial-faf5-p-284.html">Emporio Armani Replica Watch Working Chronograph Brown Dial [faf5]</a></td><td style="display:block;float:left;width:24.5%;"><div style="width:160px;height:200px;"><a href="http://www.replicwatches.com/emporio-armani-replica-watch-classic-white-dial-485c-p-263.html"><img src="http://www.replicwatches.com/images/_small//watches_22/Emporio-Armani/Emporio-Armani-Replica-Watch-Classic-White-Dial.jpg" alt="Emporio Armani Replica Watch Classic White Dial [485c]" title=" Emporio Armani Replica Watch Classic White Dial [485c] " width="160" height="120" /></a></div><a href="http://www.replicwatches.com/emporio-armani-replica-watch-classic-white-dial-485c-p-263.html">Emporio Armani Replica Watch Classic White Dial [485c]</a></td></table></div> <div id="productReviewLink" class="buttonRow back"><a href="http://www.replicwatches.com/index.php?main_page=product_reviews_write&products_id=272"><img src="http://www.replicwatches.com/includes/templates/polo/buttons/english/button_write_review.gif" alt="Write Review" title=" Write Review " width="98" height="19" /></a></div><br class="clearBoth" /></form></div></td> </tr></table></div><div id="navSuppWrapper"><br class="clearBoth" /> <div id="navSupp" style=" margin-bottom:10px; margin-top:8px; width:100%; text-align:center;"><a style="color:#000; font:12px;" href="http://www.replicwatches.com/index.php">Home</a>  <a style="color:#000; font:12px;" href="http://www.replicwatches.com/index.php?main_page=shippinginfo">Shipping</a>  <a style="color:#000; font:12px;" href="http://www.replicwatches.com/index.php?main_page=Payment_Methods">Wholesale</a>  <a style="color:#000; font:12px;" href="http://www.replicwatches.com/index.php?main_page=shippinginfo">Order Tracking</a>  <a style="color:#000; font:12px;" href="http://www.replicwatches.com/index.php?main_page=Coupons">Coupons</a>  <a style="color:#000; font:12px;" href="http://www.replicwatches.com/index.php?main_page=Payment_Methods">Payment Methods</a>  <a style="color:#000; font:12px;" href="http://www.replicwatches.com/index.php?main_page=contact_us">Contact Us</a>  </div><div style=" margin-bottom:10px; margin-top:10px; width:100%; text-align:center;"> <a style="font-weight:bold; color:#000;" href="http://www.babel-e.com" target="_blank">REPLICA OMEGA</a>    <a style="font-weight:bold; color:#000;" href="http://www.babel-e.com" target="_blank">REPLICA PATEK PHILIPPE </a>    <a style="font-weight:bold; color:#000;" href="http://www.babel-e.com" target="_blank">REPLICA ROLEX </a>    <a style="font-weight:bold; color:#000;" href="http://www.babel-e.com" target="_blank">REPLICA WATCHES </a>   <a style="font-weight:bold; color:#000;" href="http://www.babel-e.com" target="_blank">REPLICA BREITLING </a>  </div><DIV align="center"> <a href="http://www.replicwatches.com/emporio-armani-replica-watch-ss-case-black-dial-stick-marking-aa70-p-272.html" ><IMG src="http://www.replicwatches.com/includes/templates/polo/images/payment.png"></a> </DIV><div align="center" style="color:#000;">Copyright © 2012-2015 All Rights Reserved. </div></div></div><strong><a href="http://www.replicwatches.com/">best swiss replica watches</a></strong><br><strong><a href="http://www.replicwatches.com/">best replica watches</a></strong><br>
Go to Top of Page
   Topic Next 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.05 seconds. Powered By: Snitz Forums 2000