Solved

sql server date function

Posted on 2016-08-26
3
28 Views
Last Modified: 2016-08-26
I'm using sql server 2008

I have a table that looks like this:

my table
Here is the script to create the table:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Orders](
	[OrderID] [int] IDENTITY(1,1) NOT NULL,
	[CustomerID] [nchar](5) NULL,
	[EmployeeID] [int] NULL,
	[OrderDate] [datetime] NULL,
	[RequiredDate] [datetime] NULL,
	[ShippedDate] [datetime] NULL,
	[ShipVia] [int] NULL,
	[Freight] [money] NULL,
	[ShipName] [nvarchar](40) NULL,
	[ShipAddress] [nvarchar](60) NULL,
	[ShipCity] [nvarchar](15) NULL,
	[ShipRegion] [nvarchar](15) NULL,
	[ShipPostalCode] [nvarchar](10) NULL,
	[ShipCountry] [nvarchar](15) NULL,
 CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 
(
	[OrderID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Orders] ON
INSERT [dbo].[Orders] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry]) VALUES (10248, N'VINET', 5, CAST(0x0000A63900000000 AS DateTime), CAST(0x0000A65500000000 AS DateTime), CAST(0x0000A64500000000 AS DateTime), 3, 32.3800, N'Vins et alcools Chevalier', N'59 rue de l''Abbaye', N'Reims', NULL, N'51100', N'France')
INSERT [dbo].[Orders] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry]) VALUES (10249, N'TOMSP', 6, CAST(0x0000A63A00000000 AS DateTime), CAST(0x0000A66400000000 AS DateTime), CAST(0x0000A63F00000000 AS DateTime), 1, 11.6100, N'Toms Spezialitäten', N'Luisenstr. 48', N'Münster', NULL, N'44087', N'Germany')
INSERT [dbo].[Orders] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry]) VALUES (10250, N'HANAR', 4, CAST(0x0000A63D00000000 AS DateTime), CAST(0x0000A65900000000 AS DateTime), CAST(0x0000A64100000000 AS DateTime), 2, 65.8300, N'Hanari Carnes', N'Rua do Paço, 67', N'Rio de Janeiro', N'RJ', N'05454-876', N'Brazil')
INSERT [dbo].[Orders] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry]) VALUES (10251, N'VICTE', 3, CAST(0x0000A63D00000000 AS DateTime), CAST(0x0000A65900000000 AS DateTime), CAST(0x0000A64400000000 AS DateTime), 1, 41.3400, N'Victuailles en stock', N'2, rue du Commerce', N'Lyon', NULL, N'69004', N'France')
INSERT [dbo].[Orders] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry]) VALUES (10252, N'SUPRD', 4, CAST(0x0000A63E00000000 AS DateTime), CAST(0x0000A65A00000000 AS DateTime), CAST(0x0000A64000000000 AS DateTime), 2, 51.3000, N'Suprêmes délices', N'Boulevard Tirou, 255', N'Charleroi', NULL, N'B-6000', N'Belgium')
INSERT [dbo].[Orders] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry]) VALUES (10253, N'HANAR', 3, CAST(0x0000A63F00000000 AS DateTime), CAST(0x0000A64D00000000 AS DateTime), CAST(0x0000A64500000000 AS DateTime), 2, 58.1700, N'Hanari Carnes', N'Rua do Paço, 67', N'Rio de Janeiro', N'RJ', N'05454-876', N'Brazil')
INSERT [dbo].[Orders] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry]) VALUES (10254, N'CHOPS', 5, CAST(0x0000A64000000000 AS DateTime), CAST(0x0000A65C00000000 AS DateTime), CAST(0x0000A64C00000000 AS DateTime), 2, 22.9800, N'Chop-suey Chinese', N'Hauptstr. 31', N'Bern', NULL, N'3012', N'Switzerland')
INSERT [dbo].[Orders] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry]) VALUES (10255, N'RICSU', 9, CAST(0x0000A64100000000 AS DateTime), CAST(0x0000A65D00000000 AS DateTime), CAST(0x0000A64400000000 AS DateTime), 3, 148.3300, N'Richter Supermarkt', N'Starenweg 5', N'Genève', NULL, N'1204', N'Switzerland')
INSERT [dbo].[Orders] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry]) VALUES (10256, N'WELLI', 3, CAST(0x0000A64400000000 AS DateTime), CAST(0x0000A66000000000 AS DateTime), CAST(0x0000A66500000000 AS DateTime), 2, 13.9700, N'Wellington Importadora', N'Rua do Mercado, 12', N'Resende', N'SP', N'08737-363', N'Brazil')
INSERT [dbo].[Orders] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry]) VALUES (10257, N'HILAA', 4, CAST(0x0000A64500000000 AS DateTime), CAST(0x0000A66100000000 AS DateTime), CAST(0x0000A66A00000000 AS DateTime), 3, 81.9100, N'HILARION-Abastos', N'Carrera 22 con Ave. Carlos Soublette #8-35', N'San Cristóbal', N'Táchira', N'5022', N'Venezuela')
INSERT [dbo].[Orders] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry]) VALUES (10258, N'ERNSH', 1, CAST(0x0000A64600000000 AS DateTime), CAST(0x0000A66200000000 AS DateTime), CAST(0x0000A66B00000000 AS DateTime), 1, 140.5100, N'Ernst Handel', N'Kirchgasse 6', N'Graz', NULL, N'8010', N'Austria')
INSERT [dbo].[Orders] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry]) VALUES (10259, N'CENTC', 4, CAST(0x0000A64700000000 AS DateTime), CAST(0x0000A66300000000 AS DateTime), CAST(0x0000A66E00000000 AS DateTime), 3, 3.2500, N'Centro comercial Moctezuma', N'Sierras de Granada 9993', N'México D.F.', NULL, N'05022', N'Mexico')
INSERT [dbo].[Orders] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry]) VALUES (10260, N'OTTIK', 4, CAST(0x0000A64800000000 AS DateTime), CAST(0x0000A66400000000 AS DateTime), CAST(0x0000A67100000000 AS DateTime), 1, 55.0900, N'Ottilies Käseladen', N'Mehrheimerstr. 369', N'Köln', NULL, N'50739', N'Germany')
INSERT [dbo].[Orders] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry]) VALUES (10261, N'QUEDE', 4, CAST(0x0000A64800000000 AS DateTime), CAST(0x0000A66400000000 AS DateTime), CAST(0x0000A68900000000 AS DateTime), 2, 3.0500, N'Que Delícia', N'Rua da Panificadora, 12', N'Rio de Janeiro', N'RJ', N'02389-673', N'Brazil')
INSERT [dbo].[Orders] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry]) VALUES (10262, N'RATTC', 8, CAST(0x0000A64B00000000 AS DateTime), CAST(0x0000A66700000000 AS DateTime), CAST(0x0000A68C00000000 AS DateTime), 3, 48.2900, N'Rattlesnake Canyon Grocery', N'2817 Milton Dr.', N'Albuquerque', N'NM', N'87110', N'USA')
INSERT [dbo].[Orders] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry]) VALUES (10263, N'ERNSH', 9, CAST(0x0000A64C00000000 AS DateTime), CAST(0x0000A66800000000 AS DateTime), CAST(0x0000A69100000000 AS DateTime), 3, 146.0600, N'Ernst Handel', N'Kirchgasse 6', N'Graz', NULL, N'8010', N'Austria')
INSERT [dbo].[Orders] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry]) VALUES (10264, N'FOLKO', 6, CAST(0x0000A64D00000000 AS DateTime), CAST(0x0000A66900000000 AS DateTime), CAST(0x0000A6A800000000 AS DateTime), 3, 3.6700, N'Folk och fä HB', N'Åkergatan 24', N'Bräcke', NULL, N'S-844 67', N'Sweden')
INSERT [dbo].[Orders] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry]) VALUES (10265, N'BLONP', 2, CAST(0x0000A64E00000000 AS DateTime), CAST(0x0000A66A00000000 AS DateTime), CAST(0x0000A69D00000000 AS DateTime), 1, 55.2800, N'Blondel père et fils', N'24, place Kléber', N'Strasbourg', NULL, N'67000', N'France')
INSERT [dbo].[Orders] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry]) VALUES (10266, N'WARTH', 3, CAST(0x0000A64F00000000 AS DateTime), CAST(0x0000A67900000000 AS DateTime), CAST(0x0000A6B000000000 AS DateTime), 3, 25.7300, N'Wartian Herkku', N'Torikatu 38', N'Oulu', NULL, N'90110', N'Finland')
SET IDENTITY_INSERT [dbo].[Orders] OFF

Open in new window


The important column I'm working is the ShippedDate column.

What's the syntax for a query to return the following:

The number of records where ShippedDate is less than the current date(using the date function to get today’s date).

Since today's date is 8/26/2016

The query should return a count of 11

Any ideas of the syntax?
0
Comment
Question by:maqskywalker
3 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 41772317
SELECT COUNT(*) FROM Orders WHERE ShippedDate < CAST(GETDATE() as date) 

Open in new window

0
 
LVL 1

Author Closing Comment

by:maqskywalker
ID: 41772339
Thanks.
0
 
LVL 21

Expert Comment

by:Tapan Pattanaik
ID: 41772340
SELECT COUNT(*) FROM Orders WHERE ShippedDate < REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '/', '-')
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now