Looking for advice on DATE_FORMAT()

Question

Looking for advice on DATE_FORMAT()

chef423

SSCertifiable

Points: 5030
More actions
October 2, 2014 at 11:19 am

#299844

SELECT c.cust_fullname AS Name,
c.cust_membership_id AS Account,
t.c_amount AS Amount,
t.i_ticket_id AS Doc_Num,
t.s_credit_tran_type AS Detail_Account,
t.dt_when AS SaleDate
FROM Transactions AS t
INNER JOIN Customers AS c
ON t.s_ref_num = c.cust_id
WHERE s_credit_tran_type = 'House Account'
AND b_cancel = 0
AND t.[dt_when] > = dateadd(dd,((datediff(dd,'17530101',getdate())/7)*7)-7,'17530101') -- Date greater than or equal to Monday of last week
AND t.[dt_when] < = dateadd(dd,((datediff(dd,'17530101',getdate())/7)*7),'17530101') -- Date before Monday of this week
GROUP BY c.cust_fullname,
c.cust_membership_id,
t.c_amount,
t.i_ticket_id,
t.s_credit_tran_type,
t.dt_when
2014-09-23 09:48:35.817
2014-09-22 16:40:06.110
2014-09-25 09:39:32.020
2014-09-23 09:46:54.190
Above is the Date Format from my SQL query outputs, but my .CSV file will not display it correctly and I don't need the time...
I only need my Date to display in a .CSV file as '09-22-2014'
Can the DATE_FORMAT() function achieve this?
Where would I put this in my code?
Thank you.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

DATE_FORMAT is from MySQL and MariaDB. What are you using?

For SQL Server most of us use CONVERT() along with the adequate format code. http://msdn.microsoft.com/en-us/library/ms187928.aspx

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

chef423 SSCertifiable Points: 5030 More actions · Answer 2

Oh darn, I always code in SQL Sever.

Seems like CONVERT() will work? Where to place in my code? In the same line as the call for the dt_when (time/date)?

Thanks Luis.

CONVERT(varchar(10),t.dt_when)

This seems to work...

Thanks!

Xedni SSCertifiable Points: 6951 More actions · Answer 3

It would just go in your list of select columns. So from your query, you'd do

SELECT c.cust_fullname AS Name,

c.cust_membership_id AS Account,

t.c_amount AS Amount,

t.i_ticket_id AS Doc_Num,

t.s_credit_tran_type AS Detail_Account,

convert(varchar(10), t.dt_when, 110) AS SaleDate

--...

Note the "110" at the end of the convert statement. This is documented in the link Luis sent out. It informs the conversion to varchar what mask to use when building the date. Since you mentioned you wanted it in the form 09-22-2014, if you look through the different options, you'll see that 110 matches mm-dd-yyyy. If you need a different format, you can play around with that value to see how it alters the output string.

Executive Junior Cowboy Developer, Esq.[/url]

chef423 SSCertifiable Points: 5030 More actions · Answer 4

chef423

SSCertifiable

Points: 5030

October 2, 2014 at 1:53 pm

#1749804

Thank you!