SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Date format query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masterdineen
Aged Yak Warrior

United Kingdom
549 Posts

Posted - 07/03/2013 :  09:13:44  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 07/03/2013 :  09:20:13  Show Profile  Reply with Quote
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
Go to Top of Page

masterdineen
Aged Yak Warrior

United Kingdom
549 Posts

Posted - 07/03/2013 :  09:27:41  Show Profile  Reply with Quote
no, if the date is 440125 then the date will be 25-01-1944
Go to Top of Page

masterdineen
Aged Yak Warrior

United Kingdom
549 Posts

Posted - 07/03/2013 :  09:30:04  Show Profile  Reply with Quote
the format is an IBM output
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/03/2013 :  09:33:31  Show Profile  Reply with Quote
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
Go to Top of Page

masterdineen
Aged Yak Warrior

United Kingdom
549 Posts

Posted - 07/03/2013 :  09:33:35  Show Profile  Reply with Quote
the date is a data of birth,

so any year that is greater than current year would be 19xx
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/03/2013 :  09:39:10  Show Profile  Reply with Quote
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
Go to Top of Page

masterdineen
Aged Yak Warrior

United Kingdom
549 Posts

Posted - 07/03/2013 :  09:49:08  Show Profile  Reply with Quote
is there a any other settings
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/03/2013 :  10:15:49  Show Profile  Reply with Quote
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
Go to Top of Page

masterdineen
Aged Yak Warrior

United Kingdom
549 Posts

Posted - 07/04/2013 :  04:55:06  Show Profile  Reply with Quote
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)
Go to Top of Page

masterdineen
Aged Yak Warrior

United Kingdom
549 Posts

Posted - 07/04/2013 :  04:58:30  Show Profile  Reply with Quote
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)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/04/2013 :  05:01:20  Show Profile  Reply with Quote
whats was the issue with my suggestion?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

masterdineen
Aged Yak Warrior

United Kingdom
549 Posts

Posted - 07/04/2013 :  05:57:54  Show Profile  Reply with Quote
it didn't account for 2000 years, but fancied a go myself lol
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/04/2013 :  07:51:20  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000