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.
Author |
Topic |
deanfp
Starting Member
26 Posts |
Posted - 2013-04-18 : 09:58:48
|
HiI am running a SQL script that contains code that selects values from a date table. The code iswhere Orders.OrderDate BETWEEN '2012-10-01' AND '2013-04-01'When the code is run it shows the datestamp on the recordexample2012-10-01 00:19:07.623How 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' |
|
|
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) thisSELECT 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 15Incorrect syntax near 'cast'.If I use SELECT cast(OrderDate as DATE) I get this Msg 207, Level 16, State 1, Line 15Invalid column name 'OrderDate'. |
|
|
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 Pricefrom Ordersleft outer join Orders_ShoppingCarton Orders_ShoppingCart.OrderNumber=Orders.OrderNumberwhere Orders.OrderDate >= '20121001' and Orders.OrderDate < '20130401' |
|
|
deanfp
Starting Member
26 Posts |
Posted - 2013-04-18 : 11:04:29
|
Much appreciate thanks |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-04-18 : 11:32:02
|
You are very welcome - glad to help. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
|
|
|