Using the getdate() + 2 sql statment when field is not a date

I am working on a sql querry where I want the appointment scheduled for two days from today to be pulled and exported to a file.    I can use the Appt_date field.  The format for this field is 20130830.

I need to use the getdate() + 2 statement so I don't have to keep entering the date in this above format.

I am also using SQL 2008.  Is there a comand to automatically export the results of this querry to an xls file?

Any help would be appreciated.
Nancy VillaDirector of ITAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DBAduck - Ben MillerPrincipal ConsultantCommented:
SELECT CONVERT(char(8), getdate() + 2, 112) as NewDate

This will get you YYYYMMDD format from the current date + 2 days.
0
Brian CroweDatabase AdministratorCommented:
If this is a one-time effort then it is just easier to copy the query results from the grid to a file using copy/paste or the "Save Results As" menu option.  If this process is going to be a regular operation then I would suggest creating an SSIS package.

Another quick alternative is the bcp utility:

bcp "SELECT column1, column2, FROM myTable WHERE CAST(DateField AS DATETIME) = DATEADD(DAY, 2, GETDATE())" queryout myFile.csv -c -t ,
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nancy VillaDirector of ITAuthor Commented:
Is bcp something I have to download or does it come with sql 2008?
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

DBAduck - Ben MillerPrincipal ConsultantCommented:
bcp is a utility that should be included in your install of SQL Server.
0
Nancy VillaDirector of ITAuthor Commented:
it get incorrect syntax error message

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'bcp'.
Msg 103, Level 15, State 4, Line 3
The identifier that starts with 'select LM.location_name, R.description, A.appt_date, A.begintime, P.last_name, P.first_name, P.middle_name, E.event, P.contact_p' is too long. Maximum length is 128.

This is my querry.  The querry works to get the data and I just enclosed it in the quotes.

bcp "select LM.location_name, R.description, A.appt_date, A.begintime, P.last_name, P.first_name, P.middle_name, E.event, P.contact_pref_desc,P.home_phone,P.day_phone, P.cell_phone, P.email_address
from appointments A
join location_mstr LM
on A.location_id=LM.location_id
join person P
on A.person_id=P.person_id
join events E
on A.event_id = E.event_id
join appointment_members AM
on A.appt_id=AM.appt_id
join resources R
on AM.resource_id = R.resource_id
Where A.appt_date = (select convert(varchar(8),  dateadd(dd, 2, GETDATE()), 112)) and A.practice_id = '0001' and A.delete_ind = 'N' and A.cancel_ind = 'N' and A.resched_ind = 'N'
and (E.event_id Not in ('7B936EA8-BE3B-44ED-8670-8278996B962C','E261E25E-0112-42E6-8711-FEB8A8F133C6','A70E8966-3EC5-48A3-9699-2960174C5785'))
and (R.description Not in ('Sono Tech Machine OB2','Sono Tech Machine OB4','Sono Machine OB2','Sono Machine OB4','Procedure Room OB4','Sono Tech Machine OB5','Procedure Room OB2'))"
queryout \\ngimage\nextgenroot\televox.csv -c -t
0
DBAduck - Ben MillerPrincipal ConsultantCommented:
You will want to specify the -t ,  so that you get a comma delimited file.

Or if you want a tab delimited file, take off the -t and leave the -c
0
Brian CroweDatabase AdministratorCommented:
You will have to use xp_cmdshell from within SSMS and you might need to enable it first.

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

Open in new window


DECLARE @bcpCommand VARCHAR(2000)

SET @bcpCommand = 'bcp "<your query here>" queryout \\ngimage\nextgenroot\televox.csv -U <username>, -P <password> -c -t ,'
	
EXECUTE master..xp_cmdshell @bcpCommand

Open in new window


Don't forget to replace single quotes embedded in query with two single-quotes.

Adjust the username/password to use -T if you are using integrated security.  You may need to specify the server using -S.
0
Nancy VillaDirector of ITAuthor Commented:
I get the following message, but no file is created.

usage: bcp {dbtable | query} {in | out | queryout | format} datafile
  [-m maxerrors]            [-f formatfile]          [-e errfile]
  [-F firstrow]             [-L lastrow]             [-b batchsize]
  [-n native type]          [-c character type]      [-w wide character type]
  [-N keep non-text native] [-V file format version] [-q quoted identifier]
  [-C code page specifier]  [-t field terminator]    [-r row terminator]
  [-i inputfile]            [-o outfile]             [-a packetsize]
  [-S server name]          [-U username]            [-P password]
  [-T trusted connection]   [-v version]             [-R regional enable]
  [-k keep null values]     [-E keep identity values]
  [-h "load hints"]         [-x generate xml format file]
  [-d database name]
NULL
0
Brian CroweDatabase AdministratorCommented:
Yeah I got the same thing a few times.  You need to play with the flags a bit to fit your situation and authentication.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.