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
 General SQL Server Forums
 Script Library
 Convert to Proper Date		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

madhivanan
Premature Yak Congratulator

India
22460 Posts
Posted - 04/16/2007 :  10:33:41  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
I read some questions where questioners ask "Sometimes client gives data where dates are expressed as float or integer values.
How do I find maximum date?". 

Ex 
March 02, 2006 can be expressed as
	02032006.0
	020306
	2032006
	20306
	020306.0000
	2032006
Assuming the values are expressed in dmy format


The possible way is convert that value into proper date so that all types of date related calculations can be done
Create function proper_date (@date_val varchar(25))
returns datetime 
as
Begin
	Select @date_val=
			case 	when @date_val  like '%.0%' then substring(@date_val,1,charindex('.',@date_val)-1)
				else @date_val
			end
	return
		cast(
			case 	
				when @date_val like '%[a-zA-Z-/]%' then case when ISDATE(@date_val)=1 then @date_val else NULL end
				when len(@date_val)=8 then right(@date_val,4)+'-'+substring(@date_val,3,2)+'-'+left(@date_val,2)
				when len(@date_val)=7 then right(@date_val,4)+'-'+substring(@date_val,2,2)+'-0'+left(@date_val,1)
				when len(@date_val)=6 then 
					case 	when right(@date_val,2)<50 then '20'
						else '19'
					end
					+right(@date_val,2)+'-'+substring(@date_val,3,2)+'-'+left(@date_val,2)
				when len(@date_val)=5 then 
					case 	when right(@date_val,2)<50 then '20'
						else '19'
					end
					+right(@date_val,2)+'-'+substring(@date_val,2,2)+'-0'+left(@date_val,1)
			else
				case when ISDATE(@date_val)=1 then @date_val else NULL end 
			end
		as datetime
		)
End

This function will convert them into proper date
select 
	dbo.proper_date('02032006.0') as proper_date,
	dbo.proper_date('020306.000') as proper_date,
	dbo.proper_date('02032006') as proper_date,
	dbo.proper_date('020306') as proper_date,
	dbo.proper_date('20306') as proper_date,
	dbo.proper_date('020306') as proper_date

Apart from converting integer or float values to date, it will also convert date strings to date
Select 
        dbo.proper_date('March 2, 2006') as proper_date,
	dbo.proper_date('2 Mar, 2006') as proper_date,
	dbo.proper_date('2006 Mar 2') as proper_date,
	dbo.proper_date('2-Mar-2006') as proper_date,
	dbo.proper_date('3/02/2006') as proper_date,
	dbo.proper_date('02-03-2006') as proper_date,
	dbo.proper_date('2006/03/02') as proper_date,
	dbo.proper_date('March 2006') as proper_date,
	dbo.proper_date('2 Mar 2006') as proper_date


Madhivanan

Failing to plan is Planning to fail
Edited by - madhivanan on 10/08/2007 05:57:56

Kristen
Test

United Kingdom
22191 Posts
Posted - 10/08/2007 :  04:31:42  Show Profile  Reply with Quote
Madhi:

You may want to cut the "pubs." out of there - in case folk don't create the function in Pubs DB

Kristen
Edited by - Kristen on 10/08/2007 08:09:14
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22460 Posts
Posted - 10/08/2007 :  05:58:56  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by Kristen

Madhi:

You may want to cut the "pubs." out of there - in case folk don't create the function in Pubs DB

Kristen

Thanks Kristen. Done

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

kifeda
Posting Yak Master

136 Posts
Posted - 11/15/2007 :  11:18:36  Show Profile  Reply with Quote
madhivanan, your example is what I need. I have two columns, dtstartDate, and dtexpirationDate in a table called sheet3$. Both of the fields are dates, but they are in the YY / MM / DD format. I need to convert this to the MM / DD /YY format. I tried using convert but since my year is in a two date format, it thinks 07/10/23 is July 10, 2023 instead of October 23, 2007. So how can I, using the example you gave, convert the date properly?
Edited by - kifeda on 11/15/2007 11:19:21
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22460 Posts
Posted - 11/19/2007 :  02:00:30  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Ex


declare @date_val varchar(10)
set @date_val='07/10/23'

select cast(
		case when left(@date_val,2)<50 then '20'
		else '19' end+left(@date_val,2)+'-'+substring(@date_val,4,2)+'-'+right(@date_val,2)
		as datetime
	)



Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

kiruthika
Yak Posting Veteran

India
67 Posts
Posted - 11/26/2007 :  04:06:23  Show Profile  Reply with Quote
Hi!
Please explain me :
when right(@date_val,2)<50 then '20'
else '19'

why you check @date_val < 50?.If yes you replace @date_val to '20'
Is there any reason behind it?

Kiruthika!
http://www.ictend.eu
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22460 Posts
Posted - 11/26/2007 :  04:30:16  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by kiruthika

Hi!
Please explain me :
when right(@date_val,2)<50 then '20'
else '19'

why you check @date_val < 50?.If yes you replace @date_val to '20'
Is there any reason behind it?

Kiruthika!
http://www.ictend.eu



If year value is expressed in two digits, then you may not know if that belongs to current or previous century. So it is considered that if the value is less than 50 then current century otherwise last century

Ex

31/12/08 => 31/12/2008
31/12/57 => 31/12/1957


Madhivanan

Failing to plan is Planning to fail
Edited by - madhivanan on 11/26/2007 04:32:52
Go to Top of Page

kiruthika
Yak Posting Veteran

India
67 Posts
Posted - 11/26/2007 :  04:49:49  Show Profile  Reply with Quote
Hi

Thank you so much madhi! I want to know more about sql server what
can i do?Now I'm working in sql 2000.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22460 Posts
Posted - 11/26/2007 :  05:11:00  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by kiruthika

Hi

Thank you so much madhi! I want to know more about sql server what
can i do?Now I'm working in sql 2000.


1 Make use of SQL Server help file
2 Visit this forum daily and read the questions and answers

Madhivanan

Failing to plan is Planning to fail
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.06 seconds. Powered By: Snitz Forums 2000