question

Green avatar image
Green asked

Query show Date with no Time output

Hi, I have the following query SELECT [Shipment Date], [Customer Reference] AS [Storefeeder Order Number], No_ + '/' + [Sell-to Customer No_] AS [Tracking Number] FROM [GB eye Ltd$Sales Shipment Header] WHERE ([Salesperson Code] = 'INT') AND ([Shipment Date] = DATEADD(day, DATEDIFF(day, 1, GETDATE()), 0)) AND ([Customer Reference] <> 'RESEND') AND ([Sell-to Customer No_] <> 'GEN0884') ORDER BY [Storefeeder Order Number] returning these results: Shipment Date,Storefeeder Order Number,Tracking Number 2014-10-20 00:00:00.000,204-2143892-6809949,SSHP1036112/GEN2452 2014-10-20 00:00:00.000,204-2143892-6809949,SSHP1035418/GEN2452 2014-10-20 00:00:00.000,564230-OLS4723283-2-3224149,SSHP1035829/GEN0882 2014-10-20 00:00:00.000,564230-OLS4723283-2-3224149,SSHP1035301/GEN0882 How do I just show the date without 00:00:00.000 Regards Andy Green
datetime
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

·
ThomasRushton avatar image
ThomasRushton answered
It depends. If you're running an older version of SQL Server, you could just [`CONVERT`][1] the datetime field in your SELECT statement: SELECT Convert(char(10), [Shipment Date], 120) ... Alternatively, if you are running on SQL 2012 and above, you can convert to a DATE... Or just tell the presentation layer what you want - ie do the formatting in your end application. [1]: http://msdn.microsoft.com/en-GB/library/ms187928.aspx
2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image JohnM commented ·
Just an FYI, SQL 2008 has a DATE data type as well. http://msdn.microsoft.com/en-us/library/bb630352(v=sql.100).aspx. ;-)
1 Like 1 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
+1 for formatting on the client. And yeah, I also agree with @johnm, if all you want is DATE, just store that.
0 Likes 0 ·
Green avatar image
Green answered
Amazing!, thanks very much for your help :-) Shipment Date,Time Despatched,Storefeeder Order Number,Tracking Number 21/10/2014,23:30,565991-OLS472375PAYPAL-37T8,SSHP1036214/GEN0882 21/10/2014,23:30,566469-OLS4723845-2-3722014,SSHP1036212/GEN0882 21/10/2014,23:30,567083-OLS472404PAYPAL-2GL3,SSHP1035789/GEN0882 21/10/2014,23:30,568234-OLS472431PAYPAL-0A37,SSHP1035809/GEN0882 21/10/2014,23:30,569271-OLS4724455-2-3726807,SSHP1036234/GEN0882 21/10/2014,23:30,570678-OLS4724707-2-2694211,SSHP1035759/GEN0882 21/10/2014,23:30,570757-OLS4724727-2-2694428,SSHP1035792/GEN0882
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.