February 19, 2009 at 11:16 am
I am trying to write a query to export data to a billing system. I need the first row to be a #Date of transactions. I have a rough query written but do not know how to format the date to be as
#DD-Month Abbreviated-YY. Below is my query to pull this data. And the result set. My date for the first row needs to be
#19-FEB-09
select '#' + convert(varchar, dbo.date_format(getdate(), 'dd-mm-yy'))
union all
select
'P' + ',' + ',' +
',' + ',' + ',' + ',' + ',' +
employee_id + ',' +
client_num + '.' + matter_num + ',' +
convert(varchar, meter_end - meter_start - meter_wasted) + ',' +
',' + ','
from master_trans
where end_date > dbo.date_format(GETDATE(), 'YYYY-MM-DD 00:00:00')
and job_type_id = 1
and date_export is null
and export_id is null
Results are:
#19-02-09
P,,,,,,,3143,999000.000,8,,,
P,,,,,,,3143,999000.000,1,,,
P,,,,,,,3130,999000.000,18,,,
P,,,,,,,5413,999000.007,38,,,
P,,,,,,,3122,553502.002,2,,,
P,,,,,,,3103,600600.000,2,,,
P,,,,,,,3122,553501.002,3,,,
P,,,,,,,3123,632800.000,2,,,
P,,,,,,,5415,999000.000,2,,,
P,,,,,,,5457,353500.034,258,,,
P,,,,,,,3118,999000.000,20,,,
P,,,,,,,3123,446700.042,10,,,
P,,,,,,,5411,999000.000,3,,,
P,,,,,,,5411,999000.000,1,,,
P,,,,,,,5411,999000.000,1,,,
P,,,,,,,5434,999000.000,2,,,
P,,,,,,,5418,999000.000,167,,,
P,,,,,,,3123,446700.042,15,,,
P,,,,,,,5415,999000.000,1,,,
P,,,,,,,5411,999000.000,10,,,
P,,,,,,,5433,999000.000,4,,,
P,,,,,,,3118,999000.000,22,,,
P,,,,,,,3120,999000.000,16,,,
P,,,,,,,3120,999000.000,5,,,
P,,,,,,,3109,999000.001,89,,,
P,,,,,,,3120,999000.000,19,,,
P,,,,,,,3118,999000.000,53,,,
P,,,,,,,1540,999000.000,2,,,
P,,,,,,,5433,999000.001,8,,,
P,,,,,,,3118,999000.000,6,,,
P,,,,,,,3192,999000.003,5,,,
P,,,,,,,5418,571200.012,11,,,
P,,,,,,,3123,353600.000,3,,,
P,,,,,,,3118,999000.000,2,,,
P,,,,,,,5411,999000.000,7,,,
P,,,,,,,5415,999000.001,24,,,
P,,,,,,,5415,999000.000,3,,,
P,,,,,,,3118,999000.000,6,,,
P,,,,,,,3123,353600.000,35,,,
P,,,,,,,3171,353600.231,14,,,
P,,,,,,,3123,353600.000,1,,,
P,,,,,,,5415,999000.001,4,,,
Any assistance would be greatly appreciated.
February 19, 2009 at 11:28 am
Closest I have seen is Select CONVERT(nvarchar(30), GETDATE(), 6)
Result set would be 19 Feb 09.
Maybe someone else knows.
-Roy
February 19, 2009 at 11:33 am
Thanks, this works great except I need the - between the dd-month-yy.
February 19, 2009 at 11:37 am
you need to use a "style" in your CONVERT statement
From BOL:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
and there's a table with all sort of different numbers to use for different styles
declare @myDate datetime
set @myDate = '2008-02-22'
select convert(datetime, @myDate, 120)
Edit: sorry, that's not quite what you wanted at all - see BOL for suitable style to apply
February 19, 2009 at 11:41 am
Roy Ernest (2/19/2009)
Closest I have seen is Select CONVERT(nvarchar(30), GETDATE(), 6)Result set would be 19 Feb 09.
Maybe someone else knows.
This?
Select replace(CONVERT(nvarchar(30), GETDATE(), 6),' ','-')
February 19, 2009 at 11:44 am
That is practical. I was able to give this solution to him, but I was afraid that I might be reinventing the wheel since there might be a build in format for that.
-Roy
February 19, 2009 at 11:46 am
February 19, 2009 at 11:48 am
Thanks everyone. The following worked for me.
select convert(varchar, dbo.date_format(getdate(), 'dd-mmm-yy'))
This returned me the value 19-FEB-09, just like I needed.
February 19, 2009 at 12:32 pm
[font="Verdana"]The dbo.date_format() function you are calling is not (so far as I know) a standard piece of SQL Server functionality. It looks like a function that someone has developed for your database.
Most of us have to use convert() to format dates. Instead you have a fancy-dancy function to do it. 😀
I suspect you don't need the convert around it... it's likely that your function already returns a formatted varchar.
[/font]
February 20, 2009 at 6:08 am
You may want to check out this blog post, http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/02/19/sql-tip-keep-your-presentation-logic-in-your-presentation-layer.aspx about formatting using SQL Server.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 20, 2009 at 5:25 pm
Yes, you are correct. The original developer or our company's softwae has created this date function. However, this fancy dancy date format does require a convert otherwise the query will error out. Seems to me that the original developer could have and should have created this function so it could be used without the convert. Thanks for your links, I will check them out.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply