Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Format Date in SQL Stored Procedure

Posted on 2008-06-20
4
Medium Priority
?
913 Views
Last Modified: 2008-07-17
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!
0
Comment
Question by:TheUndecider
  • 2
4 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 21834335

select cast(convert(varchar(10), getdate(), 101) as datetime)
0
 

Author Comment

by:TheUndecider
ID: 21834643
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 21834674
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
 
LVL 4

Accepted Solution

by:
Thunder724 earned 2000 total points
ID: 21849709
select convert(varchar(10), getdate(), 101) as [datetime]
This will return 06/23/2008
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

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…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

916 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