Solved

sql server date function

Posted on 2016-08-26
3
40 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

827 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