?
Solved

How do I extract a query result of 08/27/1998 from a field of 1998-08-27 00:00:00.000 in SQL Server 2000

Posted on 2005-04-22
6
Medium Priority
?
205 Views
Last Modified: 2010-03-19
I am using a DTS package to export data to a CSV file.  Everything is perfect in the output file, however the vendor requesting the file needs the Date field to be in the mm/dd/yyyy format.
The field in the SQL2000 database is in the 1998-08-27 00:00:00.000 format.

I have tried the string functions, but they will not work on a datetime field.


What do I have to add to the query to extract the field in the correct format.

Thanks in advance.
0
Comment
Question by:colmsmith32
  • 2
  • 2
  • 2
6 Comments
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 13846060
SELECT ..., CONVERT(CHAR(10), dateField, 101), ...
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13846067
SELECT CONVERT(VARCHAR(10), YourDateField ,101)
FROM YourTable
0
 

Author Comment

by:colmsmith32
ID: 13846107
Thanks for the extremely quick response Scott !

Thanks rafrancisco also.


Does the 101 signify a specific format of output?
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 28

Expert Comment

by:rafrancisco
ID: 13846285
Yes, that's the format for mm/dd/yyyy.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 13846540
Yes.  Look in Books Online under topic "CAST and CONVERT" for the full list of possible formats; there are many more.
0
 

Author Comment

by:colmsmith32
ID: 13846609
Thanks again
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question