Format Date in SQL Stored Procedure

Hello, I have a simple question.  

How do I format a date in a stored procedure that selects some records from a table?   I need the format to be like this:

06/19/2008 0:00:00

Usually, I get the date value like this: 06/19/2008 12:00:00 AM.  I am exporting these records through a dataset in a .NET application to a text file and I  need the date format to be exacly what I need.  I don't need the 12 or the AM/PM label.  Also, the formatted date has to be a datetime field and not a text or varchar.

Thanks!
TheUndeciderAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Thunder724Connect With a Mentor Commented:
select convert(varchar(10), getdate(), 101) as [datetime]
This will return 06/23/2008
0
 
chapmandewCommented:

select cast(convert(varchar(10), getdate(), 101) as datetime)
0
 
TheUndeciderAuthor Commented:
Hello, thanks for replying.   I tried your solution and I still get the date as before: 6/20/2008 12:00:00 AM.  I still get the time as 12:00:00 AM.  

We used to export from a MS Access query to a comma delimited text file and the format Access does for date is mm/dd/yyyy h:mm:ss.  I need to recreate the same format but still keeping the date as a datetime field.  

Right now the closest I've got is to do it like this:
(convert(varchar,mydatefield)) + ' 0:00:00'

However, this makes it a text field and when exporting to the text file this field gets enclosed with quotation marks and it should not.


0
 
chapmandewCommented:
I think it has to do w/ the transfer from the db to your interface code because what I posted shows 2008-06-20 00:00:00.000 in the database for me.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.