September 10, 2009 at 8:24 am
I'm trying to create a DTS Package to run daily to export data from my SQL 2000 database to a CSV file.
This is what I'm trying to accomplish:
SELECT [dbo].[imitmidx_sql].[activity_cd] AS "activity_cd", [dbo].[imitmidx_sql].[activity_dt] AS "activity_dt", [dbo].[imitmidx_sql].[item_no] AS "item_no", [dbo].[imitmidx_sql].[item_desc_1] AS "item_desc_1"
FROM [dbo].[imitmidx_sql]
WHERE [dbo].[imitmidx_sql].[activity_dt] = '{%Current Date MM/DD/YYYY%}'
The date must be in the MM/DD/YYYY format. I am having trouble get the correct t-sql date function to accomplish where is says: {%Current Date MM/DD/YYYY%}. Anyone able to help out?
September 10, 2009 at 12:35 pm
you can try the following code instead
'{%Current Date MM/DD/YYYY%}'
Replace(convert(varchar(10),getdate(),110),'-','/')
for example you will get for today the following format
09/10/2009
I hope it will work for you
September 10, 2009 at 1:13 pm
Assuming your column [dbo].[imitmidx_sql].[activity_dt] is of a char(10) type, it should be more efficient to use
SELECT CONVERT(CHAR(10),GETDATE(),101) instead of the replace and convert functions.
However, you should think about storing date values in date format, since SQL Server usually performs better when dealing with date values.
September 10, 2009 at 1:28 pm
vallolet04, Thanks so much for your help. The query works perfect!
September 10, 2009 at 1:30 pm
No problem Marker any thime
September 10, 2009 at 1:34 pm
Lutz,
Your query works as well. Since it seems more efficient I will use the query you specified instead.
Ryan
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply