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

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Remove Date Stamp

Author  Topic 

deanfp
Starting Member

26 Posts

Posted - 2013-04-18 : 09:58:48
Hi

I am running a SQL script that contains code that selects values from a date table. The code is

where Orders.OrderDate BETWEEN '2012-10-01' AND '2013-04-01'

When the code is run it shows the datestamp on the record

example

2012-10-01 00:19:07.623

How can I just have the results converted to dd-mm-yyyy without altering the records?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-18 : 10:26:59
You can use one of the following:
cast(OrderDate as DATE) -- SQL 2008 or later

-- or

dateadd(dd,datediff(dd,0,OrderDate),0) -- older versions
Also, rather than using BETWEEN, write your where clause like shown below. The way I have written it, it will get all the data for October 2012 through March 2013.
where 
Order.OrderDate >= '20121001'
and Order.OrderDate < '20130401'
Go to Top of Page

deanfp
Starting Member

26 Posts

Posted - 2013-04-18 : 10:45:01
Thanks I did come across this in another post so far I have (without the where clause yet) this

SELECT Orders.FirstName AS first_name
,Orders.Email AS email
,Orders.OrderDate AS purchase_date
,Orders.ShippedOn AS delivery_date
,Orders.CustomerID AS customer_ref
,Orders.OrderNumber AS order_ref
,Orders_ShoppingCart.OrderedProductSKU AS sku
,Orders.ShippingZip AS postcode
, 'GBP' as currency
,Orders_ShoppingCart.OrderedProductPrice AS Price
from Orders
left outer join Orders_ShoppingCart
on Orders_ShoppingCart.OrderNumber=Orders.OrderNumber
where Orders.OrderDate BETWEEN '2011-03-31 00:00:00.000' AND '2012-04-01 00:00:00.000'
cast(OrderDate as DATE)

Error I get is Msg 102, Level 15, State 1, Line 15
Incorrect syntax near 'cast'.

If I use SELECT cast(OrderDate as DATE) I get this Msg 207, Level 16, State 1, Line 15
Invalid column name 'OrderDate'.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-18 : 10:58:03
In the select list, use the cast/convert. In the where clause, don't use the cast/convert. So your query should be:
SELECT Orders.FirstName AS first_name
,Orders.Email AS email
,CAST(Orders.OrderDate AS DATE) AS purchase_date
,Orders.ShippedOn AS delivery_date
,Orders.CustomerID AS customer_ref
,Orders.OrderNumber AS order_ref
,Orders_ShoppingCart.OrderedProductSKU AS sku
,Orders.ShippingZip AS postcode
, 'GBP' as currency
,Orders_ShoppingCart.OrderedProductPrice AS Price
from Orders
left outer join Orders_ShoppingCart
on Orders_ShoppingCart.OrderNumber=Orders.OrderNumber
where
Orders.OrderDate >= '20121001'
and Orders.OrderDate < '20130401'
Go to Top of Page

deanfp
Starting Member

26 Posts

Posted - 2013-04-18 : 11:04:29
Much appreciate thanks
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-18 : 11:32:02
You are very welcome - glad to help.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-18 : 13:25:25
quote:
Originally posted by deanfp

Much appreciate thanks



make sure you read this

http://visakhm.blogspot.in/2012/12/different-ways-to-implement-date-range.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -