Avatar of RIAS
RIAS
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Date format

Change format f the date

create table #table1 (coldate date)
insert #table1 values ('19000101'),('19000202'),(getdate())

declare @sql varchar(2000)

set @sql = 'select iif(coldate < ''19000102'','''',convert(char(10), coldate, 101)) coldate from #table1'

exec (@sql)

Open in new window


This  gives me an American format date is it possible to have date like DD-MM-YYYY

Cheers
Microsoft SQL Server

Avatar of undefined
Last Comment
Mark Wills

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Rich Weissler

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
RIAS

ASKER
Thanks Rich,
Will try and brb
RIAS

ASKER
Thanks mate, worked like a charm!
Mark Wills

I know it has been answered, just posting for completion....

Yes, STYLE code : https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql#date-and-time-styles

Note that 103 gives you slashes whereas 105 gives you hyphens

And most flexible is the format function : https://docs.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql

Heres an example
declare @coldate date = getdate()

select format(@coldate,'dd-MM-yyyy')

select format(@coldate,'d','en-gb')

select convert(char(10), @coldate, 105)

select convert(char(10), @coldate, 103)

Open in new window

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck