December 24, 2009 at 10:05 am
Hi All!
I currently have the following date stored in one of my tables:
2008-01-01 00:00:00
Anyone know of a way to convert to MM/DD/YYYY format?
I need it to show 01/01/2008 and drop the time
Thanks!
December 24, 2009 at 10:56 am
Dates are not stored as formatted strings in SQL Server. The client you are using will display the datetime value according to how the client is set up and could be affected by the localization setup on that PC.
To change how a date is displayed, you can use CONVERT in your query to convert the datetime to a specified format. There is a table of formats in books online under to topic CONVERT that will show you how to get the format you are looking for.
Note: if you are building a stored procedure or query that will be run from an application, I would recommend returning the full datetime and using the application to format the output appropriately. For example, if you are using SSRS to create a report - return the full datetime value and change the format in the report itself.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 24, 2009 at 11:19 am
Here are some samples that I refer to frequently.
select convert(varchar(20),getdate(),101) --1 --MM/DD/YYYY
select REPLACE(convert(varchar(20),getdate(),101),'/','-') --2 --MM-DD-YYYY
select convert(varchar(20),getdate(),103) --3 --DD/MM/YYYY
select convert(varchar(20),getdate(),104) --4 --DD.MM.YYYY
select convert(varchar(20),getdate(),105) --5 --DD-MM-YYYY
select convert(varchar(20),getdate(),106) --6 --DD MMM YYYY
select convert(varchar(20),getdate(),107) --7 --MMM DD, YYYY
select convert(varchar(20),getdate(),108) --8 --CURRENT TIME HH:MM:SS
select convert(varchar(30),getdate(),109) --9 --CURRENT DATE AND TIME MMM DD YYY H:MM:SS AM/PM
select convert(varchar(20),getdate(),110) --10 --MM/DD/YYYY
select convert(varchar(20),getdate(),111) --11 --YYYY/MM/DD
select convert(varchar(20),getdate(),112) --12 --YYYYMMDD
select convert(varchar(30),getdate(),113) --13 --CURRENT DATE AND TIME DD MMM YYY HH:MM:SS:MMM
select convert(varchar(30),getdate(),114) --14 --CURRENT TIME HH:MM:SS:MMM
select convert(varchar(30),getdate(),120) --20 --CURRENT DATE AND TIME YYYY-MM-DD HH:MM:SS
select convert(varchar(30),getdate(),121) --21 --CURRENT DATE AND TIME YYYY-MM-DD HH:MM:SS:MMM
December 24, 2009 at 11:57 am
Thanks, these should help!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply