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?
 
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).





0
 
BrandonGalderisiCommented:
select convert(char(10), getdate(),101)
0
 
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
HoggZillaCommented:
SELECT CONVERT(datetime,GETDATE(),103)  -- 2008-11-10 13:04:35.603
or

SELECT CONVERT(char(10),GETDATE(),103) --  10/11/2008
0
 
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!
0
 
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
0
 
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.
0
 
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.
0
 
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

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