Convert to Proper Date - 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

General SQL Server Forums

Script Library

Convert to Proper Date

Author

Topic

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-16 : 10:33:41

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

Kristen
Test

22859 Posts

Posted - 2007-10-08 : 04:31:42

Madhi:

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

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-08 : 05:58:56

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

kifeda
Posting Yak Master

136 Posts

Posted - 2007-11-15 : 11:18:36

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?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-19 : 02:00:30


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

kiruthika
Yak Posting Veteran

67 Posts

Posted - 2007-11-26 : 04:06:23

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-26 : 04:30:16

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

kiruthika
Yak Posting Veteran

67 Posts

Posted - 2007-11-26 : 04:49:49

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.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-26 : 05:11:00

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources