How to get and Format current date in MSSQL

In acces i can format the current date like so Format(Now(),'dd/mm/yyyy')

what can i use to make it work formssql ? any ideas

Many thanks
david_88Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

BrandonGalderisiCommented:
select convert(char(10), getdate(),101)
Kevin CrossChief Technology OfficerCommented:
You get the current date using GETDATE() and you can use the CONVERT function to change to specified string format.

http://msdn.microsoft.com/en-us/library/ms187928.aspx
Steve HoggITCommented:
SELECT CONVERT(datetime,GETDATE(),103)  -- 2008-11-10 13:04:35.603
or

SELECT CONVERT(char(10),GETDATE(),103) --  10/11/2008
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Kevin CrossChief Technology OfficerCommented:
HoggZilla's is what you want to use for DD/MM/YYYY format -- 103 -- other values are listed on the chart I provided.

Good luck!
rameshvelayudhanData ArchitectCommented:
You can use a number of formats style for converting a Date value to retrieve either the date part or time or both, and here are some of them:

Select Convert(Varchar,GetDate(),101)

Select Convert(Varchar,GetDate(),112)

Select Convert(Varchar,GetDate(),114)

Select Convert(Varchar,GetDate(),121)

Additionally, take a look at the URL's below for further examples

http://www.databasejournal.com/features/mssql/article.php/2197931/Working-with-SQL-Server-DateTime-Variables-Part-Two---Displaying-Dates-and-Times-in-Different-Formats.htm

http://www.dreamincode.net/forums/showtopic42872.htm
Kevin CrossChief Technology OfficerCommented:
@rameshvelayudhan, you posted some really great examples but none of those formats to DD/MM/YYYY.  

I already posted a link to all the possible formats, but http:#22923864 is the answer.
BrandonGalderisiCommented:
You would be best served to leave the formatting of information to your front end (or report) and leave the data in the datetime format for export from SQL Server.
Mark WillsTopic AdvisorCommented:
There is only one that does DD/MM/YYYY and that is 103, trouble is it will also do time, so, you have to get rid of that first... or... convert to a string field (and loose date arithmetic). And hogzilla posted it correctly first, but didn't explain...

so, for "current" datetime, we use :    getdate()

then using the convert function to express that as a format :

select convert(varchar(10), getdate(), 103)    -- gives 17/11/2008 - but no longer a recognise datetime column, so if you want to do arithmetic, best to use international format ie:

select convert(varchar(8), getdate(), 112)

with that you can still add a day, or a time - for example tomorrow...

select convert(varchar(8), getdate(), 112) + 1  -- cannot do that with format 103

or for end of day...

select convert(varchar(8), getdate(), 112) + ' 23:59:59'

but if we want to add time as well, then we have to use date functions for part of it (can append time, or add days, but not both without some help)

select dateadd(dd,1, convert(varchar(8), getdate(), 112) + ' 23:59:59' )


The Format number really just tells the convert function how to format the string. If there are no strings, then it really isn't needed (ie it is in datetime already). That format number must match exactly otherwise you do get errors - unless of course it is international format of yyyymmdd  which is always understood and can be explicitly converted. Similarly, format 106 which is dd MMM yyyy (e.g. 17 Nov 2008) is also understood, and the one I tend to use all the time (or the international), however, is constrained by language settings (as in different languages do spell the months differently).





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
BrandonGalderisiCommented:
The problem with doing "MATH" on the format 112 without converting it to a datetime is the last day of the month will increment to an invalid date.  

See below:

declare @TheDate datetime
set @TheDate = convert(datetime, '06/30/2008',101)
 
 
select @theDate, convert(varchar(8), @TheDate, 112) + 1, @TheDate+1

Open in new window

david_88Author Commented:
thanks most detailed answer and helped a great deal thanks
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 SQL Server

From novice to tech pro — start learning today.