SQL date format dd/mm/yy

Hi,

I've already searched for this question, and tried the code in the answers given, but I still can't get it to work.

I'm trying to display the current date in the format dd/mm/yy, with no time.

Here is the code I've tried so far:

--set dateformat dmy DECLARE @today as date --set @today = CONVERT(date, getdate()) as [dd/mm/yy] set @today = CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]

print @today

The commented out lines are previous attempts. When I run the code as it is above, I get the following error message:

Incorrect syntax near the keyword 'AS'.

DECLARE @today as date
set @today = CONVERT(date, getdate()) 
set dateformat dmy
The code above results in:

2012-06-06

but I need the format to be 06-06-2012.

Thanks

June 6th, 2012 9:56am

Hi,

Try this

DECLARE @today date
set @today = CONVERT(date, getdate())CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]
Print @today

Free Windows Admin Tool Kit Click here and download it now
June 6th, 2012 10:04am

Hi,

Try this

SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY]

Regards
Satheesh


June 6th, 2012 10:27am

SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY]

Thanks, that line works, but I want to place it into the @today variable. When I write it like this, I get an error:

DECLARE @today date
SET @today = CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY]
Print @today

I get the following error:

Incorrect syntax near the keyword 'SELECT'.

The following code works, but the date format is yyyy-mm-dd

DECLARE @today date
SET @today = (SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY])
Print @today

The output is
2012-06-06 
Free Windows Admin Tool Kit Click here and download it now
June 6th, 2012 10:44am

Try this

You cannot declare @today as DATE as you will lose the formatting

DECLARE @today varchar(10)
SET @today = CONVERT(VARCHAR(10), GETDATE(), 105)
Print @today


Regards
satheesh

  • Marked as answer by aspNewbie1978 Wednesday, June 06, 2012 11:01 AM
June 6th, 2012 10:50am

Thanks, that worked. I can't see the button to mark your post as the answer though.
Free Windows Admin Tool Kit Click here and download it now
June 6th, 2012 10:53am

Hi,

You posted this question as a discussion, thats why no "Mark as Answer", You can try changing the post type to Question. :-)

Regards
Satheesh


June 6th, 2012 10:58am

I've done that now.

When starting this thread, I clicked on the 'Ask a Question' button, so I thought it would be posted as the correct type.

Free Windows Admin Tool Kit Click here and download it now
June 6th, 2012 11:02am

Thanks you :-)
June 6th, 2012 11:03am

your code work's fine but i want to display date format like 28-Mar-2015.

have any idea how to display like this?

Free Windows Admin Tool Kit Click here and download it now
March 28th, 2015 6:53am

Good day 

I hope that you are still here and read this, since there is a basic reason for the error that you got in the start. and i dont see anyone mentioned it. The idea is not just to copy a solution that someone give you but to understand :-)
I hope this is  abit useful

>> When I run the code as it is above, I get the following error message:Incorrect syntax near the keyword 'AS'.

The reason for the error in the first code that you post has nothing to do with the date or date format. It is raising since you tried to use SET parameter and you used "as ...". this is not a select query and you can not use this format: "SET @X = ... as name"

* You use the CONVERT style parameter(103 in your case), during the display format for example when you convert it to string (the select query), but in your case you print the date in the end without any specific display style, and therefore it is printed as your current configur

March 28th, 2015 4:07pm

It is worth to note that the output of these date formats are of VARCHAR data types already and not of DATETIME data type.  With this in mind, any date comparisons performed after the datetime value has been formatted are using the VARCHAR value of the date and time and not its original DATETIME value.

Details :

http://www.sql-server-helper.com/tips/date-formats.aspx

Free Windows Admin Tool Kit Click here and download it now
March 29th, 2015 12:36am

Hello ,

You want to use the "British/French" format ("DD/MM/YY") .Now see the below Script :

SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY]

Output :

29/03/15

March 29th, 2015 12:43am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics