Date format query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Date format query

Author

Topic

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-07-03 : 09:13:44

hello there.

I have a list of dates that have come through as

eg 440125 , 020618 yymmdd

how would I convert to dd-mm-yyyy

regards

Rob

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-03 : 09:20:13

what does the values represent?
440125 does it represent 2044-01-25 and 020618 2002-08-18?

if yes use below logic


DECLARE @Datevalue int=440125

SELECT DATEADD(dd,(@datevalue%100)-1,DATEADD(mm,((@datevalue/100)%100)-1,DATEADD(yy,CASE WHEN @datevalue/10000>50 THEN 0 ELSE 100 END + @datevalue/10000,0))) AS datevalue

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-07-03 : 09:27:41

no, if the date is 440125 then the date will be 25-01-1944

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-07-03 : 09:30:04

the format is an IBM output

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-03 : 09:33:31

quote:
Originally posted by masterdineen

no, if the date is 440125 then the date will be 25-01-1944

ok then this should be enough


DECLARE @Datevalue int=440125

SELECT DATEADD(dd,(@datevalue%100)-1,DATEADD(mm,((@datevalue/100)%100)-1,DATEADD(yy,@datevalue/10000,0))) AS datevalue

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-07-03 : 09:33:35

the date is a data of birth,

so any year that is greater than current year would be 19xx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-03 : 09:39:10

quote:
Originally posted by masterdineen

the date is a data of birth,

so any year that is greater than current year would be 19xx

sorry elaborate on that

does that mean 13 means 2013

and 14 means 1914?

In SQL Server under default settings cutoff value is 50 so anything before 50 its 20 as century ie 44 means 2044, 30 means 2030 etc
and any value > 50 means century as 19 ie 66 as 1966 etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-07-03 : 09:49:08

is there a any other settings

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-03 : 10:15:49

quote:
Originally posted by masterdineen

is there a any other settings

Nope...its a server setting

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-07-04 : 04:55:06

declare @IBMdate nVARCHAR(10)

declare @Day int

declare @month int

declare @year int

declare @UKdate date

set @IBMdate = '121129'

set @Day = right(@ibmdate,2)

set @month = (select SUBSTRING(@IBMdate, 3,2))

set @year = (select case when left (@ibmdate,2) >= right(YEAR(getdate()),2)
then N'19'+ left (@ibmdate,2) else N'20' + left (@ibmdate,2) end)

select convert(nvarchar(3),@day) +'-'+ convert(nvarchar(3),@month) +'-'+ convert(nvarchar(4),@year)

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-07-04 : 04:58:30

declare @IBMdate nVARCHAR(10)

declare @Day int

declare @month int

declare @year int

declare @UKdate date

set @IBMdate = '121129'

set @Day = right(@ibmdate,2)

set @month = (select SUBSTRING(@IBMdate, 3,2))

set @year = (select case when left (@ibmdate,2) >= right(YEAR(getdate()),2)
then N'19'+ left (@ibmdate,2) else N'20' + left (@ibmdate,2) end)

set @IBMdate = (select convert(nvarchar(3),@day) +'-'+ convert(nvarchar(3),@month) +'-'+ convert(nvarchar(4),@year))

select @IBMdate

--select convert(nvarchar(3),@day) +'-'+ convert(nvarchar(3),@month) +'-'+ convert(nvarchar(4),@year)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-04 : 05:01:20

whats was the issue with my suggestion?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-07-04 : 05:57:54

it didn't account for 2000 years, but fancied a go myself lol

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-04 : 07:51:20

quote:
Originally posted by masterdineen

it didn't account for 2000 years, but fancied a go myself lol

it will if you made a small tweak like below as per your latest explanation


DECLARE @Datevalue int=440125

SELECT DATEADD(dd,(@datevalue%100)-1,DATEADD(mm,((@datevalue/100)%100)-1,DATEADD(yy,CASE WHEN @datevalue/10000 > YEAR(GETDATE()) % 100 THEN 0 ELSE 100 END + @datevalue/10000,0))) AS datevalue

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources