If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > how to convert date string to datetime

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Mar 2007
Posts: 208
how to convert date string to datetime

I have a table in which a date value is stored as varchar.

some of these values are stored ina dd/mm/yyyy format and other values are stored in a yyyy-mm-dd format

Now I wish to retrieve some data by querying between two dates. However I need to convert the varchar date value to datetime in order to do this but since the date value is in two different formats, the following doesn't work.

select date_value
from my_table
where CONVERT(DATETIME, date_value, 103) between @date1 and @date2


How can you convert the date value to datetime when its stored in mutiple formats. I can't change the table itself as I dont have admin privelages.
Reply With Quote
  #2 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,835
Drop the format value from your convert statement, let it figure out the format.

This points out that you have a bigger problem, in that the dates are tempermental when stored in character form. You might want to as your DBA to convert these into a DATE column!

-PatP
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Mar 2007
Posts: 208
Quote:
Originally Posted by Pat Phelan View Post
Drop the format value from your convert statement, let it figure out the format.

This points out that you have a bigger problem, in that the dates are tempermental when stored in character form. You might want to as your DBA to convert these into a DATE column!

-PatP
i get out of range error if I drop the convert function because the @date1 and @date2 values are passed in dd/mm/yyyy format?
Reply With Quote
  #4 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,335
personally
Id create a new column that is of datetime datatype
update that new column with the value of the old column converting as required, use a where clause to differentiate the ISO dates from non iso dates
onbce you are certain that all date values have been updated
then drop the old column and rename the new column waht ever the old column was called

once its done I'd grasp the developer/DBA/whoever warmly by the throat and suggest they learn how DB's work before inflicting such horrors on a 'real' db. If they are unable to see the error of their ways then consider taking 'em out for a necklace party

...what me a fundalemntalist
sounds a bit extreme. An effective use of a necklace stops them repeating this sort of error
__________________
Is taking part in the 'Distinguished Gentlemans' ride. Being neither distinguished nor a gentleman, but I am keen to raise funds for Prostrate Cancer research. At present I'm threatening to ride a Norton 961 wearing a kilt. If you would like to contribute to Prostrate Cancer research then please consider donating via this page:-
http://www.gentlemansride.com/rider/healdem
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Mar 2007
Posts: 208
Quote:
Originally Posted by healdem View Post
personally
Id create a new column that is of datetime datatype
update that new column with the value of the old column converting as required, use a where clause to differentiate the ISO dates from non iso dates
onbce you are certain that all date values have been updated
then drop the old column and rename the new column waht ever the old column was called

once its done I'd grasp the developer/DBA/whoever warmly by the throat and suggest they learn how DB's work before inflicting such horrors on a 'real' db. If they are unable to see the error of their ways then consider taking 'em out for a necklace party

...what me a fundalemntalist
sounds a bit extreme. An effective use of a necklace stops them repeating this sort of error
this is returning me back to the orginal question. How do I convert to datetime when date value is stored in two different formats? copying to a new table or converting within a query is the same problem.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Location: Woodland Hills, CA
Posts: 18
Converting varchar dates when formats vary

/**
/ Purpose: Convert dates stored as varchar format to datetime value.
/ Author: YesAgile
/ Date: 2013.01.08
/
/ Notes: Works only if formats are the following:
/
/ yyyy-mm-dd
/ dd/mm/yyyy
*/
alter function dbo.fn_convertDate(@dt varchar(10), @formatCode int = 120)
returns datetime
begin
declare
@ret datetime -- Return value.
,@dtCopy varchar(10) -- Copy used for parsing.
,@dtAsInt int -- @dtCopy as int.

-- Set default date value to return.
set @ret = convert(datetime, '01/01/1900')

-- Strip the '/' and '-' from varchar date value.
set @dtCopy = replace(replace(@dt, '/', ''), '-', '')

-- Convert to int.
set @dtAsInt = convert(int, @dtCopy)

-- Is integer value a date?
-- Note that SQL dates are stored internally as numerical values
-- so passing an int to the isdate() is still valid.
if (isdate(@dtAsInt) = 1)
begin
set @ret = convert(datetime, @dt, 120)
end
else
begin
set @ret = convert(datetime, @dt, 103)
end


return @ret
end
go

select dbo.fn_convertDate('2012-12-22', default)
select dbo.fn_convertDate('22/12/2012', default)
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Location: Woodland Hills, CA
Posts: 18
how to convert date string to datetime

I agree with ozzi. The DBA group needs to have better discipline and store dates instead of strings. Please note that when using a function (UDF or system-built-in) in a SQL update, select or delete statement you are not getting the optimal performance from SQL Server.

The reason is that the function is being called for each row that qualifies and/or for every row in your WHERE clause to see if it qualifies. This has the affect of turning your query or proc into a row-by-row process instead of set based.

Hope the function works for you.
Reply With Quote
  #8 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,561
Dropping the format value ("103") from your function call should work, provided your dates are consistently in these two formats. SQL Server will implicitly convert both formats.

Code:
with TestDates as
(select	'01/08/2012' as TestDate
union select '2012-01-13')
select	TestDate,
	CONVERT(DATETIME, TestDate) as RealDate
from	TestDates
TestDate RealDate
---------- -----------------------
01/08/2012 2012-01-08 00:00:00.000
2012-01-13 2012-01-13 00:00:00.000
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Mar 2007
Posts: 208
Quote:
Originally Posted by blindman View Post
Dropping the format value ("103") from your function call should work, provided your dates are consistently in these two formats. SQL Server will implicitly convert both formats.

Code:
with TestDates as
(select	'01/08/2012' as TestDate
union select '2012-01-13')
select	TestDate,
	CONVERT(DATETIME, TestDate) as RealDate
from	TestDates
TestDate RealDate
---------- -----------------------
01/08/2012 2012-01-08 00:00:00.000
2012-01-13 2012-01-13 00:00:00.000
this doesn't work because the parameters @date1 and @date2 are entered in SSRS in the following format dd/mm/yyyy

When I run a report that uses the following query:

select date_value
from my_table
where CONVERT(DATETIME, date_value) between @date1 and @date2

I get error in SSRS stating date value out of range.

When I run the same query by itself in SQL server management studio and replace the parameter values with hardcoded values as follows it still doesnt work.

select date_value
from my_table
where CONVERT(DATETIME, date_value) between '09/01/2013' and '10/01/2013'
Reply With Quote
  #10 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,335
fix the problem at source
doping this bastsardisation every time you run the query is folly
get the data right first
fix what is causing the problem then its not an ongoing problem over time.
__________________
Is taking part in the 'Distinguished Gentlemans' ride. Being neither distinguished nor a gentleman, but I am keen to raise funds for Prostrate Cancer research. At present I'm threatening to ride a Norton 961 wearing a kilt. If you would like to contribute to Prostrate Cancer research then please consider donating via this page:-
http://www.gentlemansride.com/rider/healdem
Reply With Quote
  #11 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,835
Quote:
Originally Posted by ozzii View Post
this doesn't work because the parameters @date1 and @date2 are entered in SSRS in the following format dd/mm/yyyy
Sure it does... Make them DATE instead of VARCHAR and you can enter them however you please (assuming your locale is set correctly)!

-PatP
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
Reply With Quote
  #12 (permalink)  
Old
Registered User
 
Join Date: Mar 2007
Posts: 208
Quote:
Originally Posted by blindman View Post
Dropping the format value ("103") from your function call should work, provided your dates are consistently in these two formats. SQL Server will implicitly convert both formats.

Code:
with TestDates as
(select	'01/08/2012' as TestDate
union select '2012-01-13')
select	TestDate,
	CONVERT(DATETIME, TestDate) as RealDate
from	TestDates
TestDate RealDate
---------- -----------------------
01/08/2012 2012-01-08 00:00:00.000
2012-01-13 2012-01-13 00:00:00.000

dropping the format option from the convert function causes an out of range date error when the date is in the following format dd/mm/yyyy and where dd is greater than 12 i.e. 13/06/2012

In addtion when date is in the format dd/mm/yyyy the convert function swaps the dd and mm around so I end up with incorrect date i.e.

12/06/2012 ends up as 2012-12-06
Reply With Quote
  #13 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,561
Quote:
Originally Posted by ozzii View Post
dropping the format option from the convert function causes an out of range date error when the date is in the following format dd/mm/yyyy and where dd is greater than 12 i.e. 13/06/2012
I'm guessing here, but I bet that's because there is no 13th month.
Thirty days hath September, April June and November....well, I can't remember the rest, but google it to be sure. If there is a 13th month, then my company has been cheating me out of some paychecks I deserve.
If you have messy data, then you have messy data. Clean up the data or write a custom function like YesAgile suggested.
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com
Reply With Quote
  #14 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,335
doesn't sound like messy date, just data that isn't doesn't use the US format

something the OP pointed out in post #1
__________________
Is taking part in the 'Distinguished Gentlemans' ride. Being neither distinguished nor a gentleman, but I am keen to raise funds for Prostrate Cancer research. At present I'm threatening to ride a Norton 961 wearing a kilt. If you would like to contribute to Prostrate Cancer research then please consider donating via this page:-
http://www.gentlemansride.com/rider/healdem
Reply With Quote
  #15 (permalink)  
Old
Registered User
 
Join Date: Mar 2007
Posts: 208
Quote:
Originally Posted by blindman View Post
I'm guessing here, but I bet that's because there is no 13th month.
Thirty days hath September, April June and November....well, I can't remember the rest, but google it to be sure. If there is a 13th month, then my company has been cheating me out of some paychecks I deserve.
If you have messy data, then you have messy data. Clean up the data or write a custom function like YesAgile suggested.
The 13/06/2012 which uses the dd/mm/yyyy format (british format) translates as the 13th of June 2012 - how is that a messy date????
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On