SQL Working With Dates

The name of the pictureThe name of the pictureThe name of the pictureClash Royale CLAN TAG#URR8PPP





googletag.cmd.push(function() googletag.display('div-gpt-ad-1422003450156-2'); );



SQL Working With Dates



❮ Previous
Next ❯

SQL Dates




The most difficult part when working with dates is to be sure that the format of the date you are trying to insert,
matches the format of the date column in the database.




As long as your data contains only the date portion, your queries will work as expected. However, if a time portion is involved, it gets
more complicated.


SQL Date Data Types


MySQL comes with the following data types for storing a date or a date/time value in the database:


  • DATE - format YYYY-MM-DD

  • DATETIME - format: YYYY-MM-DD HH:MI:SS

  • TIMESTAMP - format: YYYY-MM-DD HH:MI:SS

  • YEAR - format YYYY or YY

SQL Server comes with the following data types for storing a date or a date/time value in the database:


  • DATE - format YYYY-MM-DD

  • DATETIME - format: YYYY-MM-DD HH:MI:SS

  • SMALLDATETIME - format: YYYY-MM-DD HH:MI:SS

  • TIMESTAMP - format: a unique number

Note: The date types are chosen for a column when you create a new table in your database!


SQL Working with Dates



You can compare two dates easily if there is no time component involved!




Assume we have the following "Orders" table:


















OrderIdProductNameOrderDate
1Geitost2008-11-11
2Camembert Pierrot2008-11-09
3Mozzarella di Giovanni2008-11-11
4Mascarpone Fabioli2008-10-29


Now we want to select the records with an OrderDate of "2008-11-11" from the table above.


We use the following SELECT statement:



SELECT * FROM Orders WHERE OrderDate='2008-11-11'


The result-set will look like this:












OrderIdProductNameOrderDate
1Geitost2008-11-11
3Mozzarella di Giovanni2008-11-11


Now, assume that the "Orders" table looks like this (notice the time component in the "OrderDate" column):


















OrderIdProductNameOrderDate
1Geitost2008-11-11 13:23:44
2Camembert Pierrot2008-11-09 15:45:21
3Mozzarella di Giovanni2008-11-11 11:12:01
4Mascarpone Fabioli2008-10-29 14:56:59


If we use the same SELECT statement as above:



SELECT * FROM Orders WHERE OrderDate='2008-11-11'

we will get no result! This is because the query is looking only for dates with no time portion.


Tip: To keep your queries simple and easy to maintain, do not allow time components in your dates!





❮ Previous
Next ❯

-

Popular posts from this blog

California

Image
Clash Royale CLAN TAG #URR8PPP For other uses, see California (disambiguation). State of California Flag Seal Nickname(s): The Golden State Motto(s): Eureka [1] State song(s): " I Love You, California " Official language English Spoken languages Native languages as of 2007 English 57.4% [2] Spanish 28.5% [3] Chinese 2.8% [3] Filipino 2.2% [3] Demonym Californian Capital Sacramento Largest city Los Angeles Largest metro Greater Los Angeles Area Ranked 3rd  • Total 163,696 sq mi (423,970 km 2 )  • Width 250 miles (400 km)  • Length 770 miles (1,240 km)  • % water 4.7  • Latitude 32°32′ N to 42° N  • Longitude 114°8′ W to 124°26′ W Population Ranked 1st  • Total 39,536,653 (2017 est.) [4]  • Density 240/sq mi  (92.6/km 2 ) Ranked 11th  • Median household income $66,636 ...
Read more

New York City

Image
Clash Royale CLAN TAG #URR8PPP "NYC" and "New York, New York" redirect here. For other uses, see New York City (disambiguation); NYC (disambiguation); and New York, New York (disambiguation). Largest city in the United States Megacity in United States New York City Megacity City of New York Clockwise, from top: Midtown Manhattan, Times Square, the Unisphere in Queens, the Brooklyn Bridge, Lower Manhattan with One World Trade Center, Central Park, the headquarters of the United Nations, and the Statue of Liberty Flag Seal Wordmark Nickname(s): See Nicknames of New York City Location within the U.S. state of New York New York City Location in the contiguous United States and New York Show map of the US New York City New York City (New York) Show map of New York Coordinates: 40°42′46″N 74°00′21″W  /  40.7127°N 74.0059°W  / 40.7127; -74.0059 Coordinates: 40°42′46″N 74°00′21″W  ...
Read more

CNN

Image
Clash Royale CLAN TAG #URR8PPP This article is about the channel. For other uses, see CNN (disambiguation). American news channel CNN Launched June 1, 1980 ; 38 years ago  ( 1980-06-01 ) Owned by Turner Broadcasting System Picture format 1080i (HDTV) (480i letterboxed for SDTV feed) Slogan Go There This is CNN The Most Trusted Name in News Facts First The Worldwide Leader in News Reporting from around the World Country United States Language English Broadcast area United States Canada Online (for US and Canadian pay-TV subscribers via CNNGo) Radio (news reports on the half-hour) Headquarters CNN Center Atlanta, Georgia Sister channel(s) CNN International CNN Philippines CNN Indonesia CNN-News18 CNN Airport Network CNN Türk CNN en Español HLN CNN Chile Website www.cnn.com TV schedule (North America) Availability Satellite DirecTV (U.S.) Channel 202 Channel 1202 (VOD) Dish Network Channel 200 Channel 9436 Bell TV (Canada) Channel 500 (SD) Channel 1500 (HD) Shaw Dire...
Read more