Date format

RIAS
RIAS used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Professional Troublemaker^h^h^h^h^hshooter
Commented:
RIAS

Author

Commented:
Thanks Rich,
Will try and brb
RIAS

Author

Commented:
Thanks mate, worked like a charm!
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial