SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Remove Date Stamp
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

deanfp
Starting Member

26 Posts

Posted - 04/18/2013 :  09:58:48  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 04/18/2013 :  10:26:59  Show Profile  Reply with Quote
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 - 04/18/2013 :  10:45:01  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 04/18/2013 :  10:58:03  Show Profile  Reply with Quote
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 - 04/18/2013 :  11:04:29  Show Profile  Reply with Quote
Much appreciate thanks
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 04/18/2013 :  11:32:02  Show Profile  Reply with Quote
You are very welcome - glad to help.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/18/2013 :  13:25:25  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New 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.09 seconds. Powered By: Snitz Forums 2000