• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 650
  • Last Modified:

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
1 Solution
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.

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

SELECT CONVERT(char(10),GETDATE(),103) --  10/11/2008
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

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!
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


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

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

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Tackle projects and never again get stuck behind a technical roadblock.
Join Now