Conversion of varchar to datetime
-
Hi,
When you converting varchar to date time
SELECT convert(varchar,getdate())
What is the getdate here
i.e. we are converting any value to today's date
-
ramana3327 (10/2/2014)
Hi,When you converting varchar to date time
SELECT convert(varchar,getdate())
What is the getdate here
i.e. we are converting any value to today's date
I think you are reading the order of the parameters for the convert function backwards. The first parameter is what datatype you want to convert to. In this case a varchar. An additional note on that, if you don't specify a size of varchar, it will default to varchar(30) in the convert function, so be careful. The value you are converting is getdate(), which is a function that returns the system date and time. So if you run the command you have, you will get the system date and time back as a varchar(30).
-
This is one of those rare times when you can get away without defining the scale of a varchar. When you use varchar you should ALWAYS specify the scale.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
I have a table which has manufacturing date column but they use the varchar instead of datetime datatype.
Also that has the null values.
I need to get the id values which has manufacturing date has greater than 2 yrs from todays date.
i.e. I want to delete all the records which is greater than 2 yr old.
I use the
select datediff(day, convert(varchar,MANUFACTUREDDATE, 101),convert(varchar,getdate(), 103))
from dbo.table
to find the which has 2yr difference.
Select id from table
where datediff(day, convert(varchar,MANUFACTUREDDATE, 101),convert(varchar,getdate(), 103))
from dbo.table> 2
But when I use that datediff(day, convert(varchar,MANUFACTUREDDATE, 101),convert(varchar,getdate(), 103))
from dbo.table
I am getting error msg
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
That column has null values, is that causing any errors here?
-
Probably not. And, you don't usually need to use CONVERT to cast a VARCHAR to a DATETIme when you're using things like DATEDIFF.
What you do need to do, though, is ensure that you have something as a VARCHAR that can be converted. Check the table using ISDATE(somevarcharcolumnname) to find rows that might be upsetting the apple cart.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Quick thought, what is the data type of the MANUFACTUREDDATE column? If the MANUFACTUREDDATE is a character column of the format MM/DD/YYYY (101) then you must always use the 101 directive for the conversion.
The last parameter serves two purposes, format of the source when converting into datetime type and output format when converting into a character data type. If MANUFACTUREDDATE is a datetime type datatype the conversion is useless for these purposes, datetime type data types do not have any formats!
The following is wrong
datediff(day, convert(varchar,MANUFACTUREDDATE, 101),convert(varchar,getdate(), 103))
,it is comparing MM/DD/YYYY to DD/MM/YYYY, the only thing the convert does in the query is setting the format of the output string, which then are implicitly cast to datetime values.
😎
-
ramana3327 (10/3/2014)
I have a table which has manufacturing date column but they use the varchar instead of datetime datatype.Also that has the null values.
I need to get the id values which has manufacturing date has greater than 2 yrs from todays date.
i.e. I want to delete all the records which is greater than 2 yr old.
I use the
select datediff(day, convert(varchar,MANUFACTUREDDATE, 101),convert(varchar,getdate(), 103))
from dbo.table
to find the which has 2yr difference.
Select id from table
where datediff(day, convert(varchar,MANUFACTUREDDATE, 101),convert(varchar,getdate(), 103))
from dbo.table> 2
But when I use that datediff(day, convert(varchar,MANUFACTUREDDATE, 101),convert(varchar,getdate(), 103))
from dbo.table
I am getting error msg
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
That column has null values, is that causing any errors here?
Since you are on SQL 2012, I would recommend giving TRY_PARSE a go.
Setup a simple testbed:
USE tempdbIF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.table') AND type IN (N'U') )
DROP TABLE dbo.
;
GO
CREATE TABLE dbo.
(
id INT IDENTITY(1, 1) NOT NULL,
MANUFACTUREDDATE VARCHAR(30) NULL
);
GO
INSERT INTO dbo.
(MANUFACTUREDDATE)
VALUES ('1/1/2015'),
('not a date'),
(NULL);
GO
Try it:
-- show our test dataSELECT *
FROM dbo.
;
-- find rows with a string that cannot be converted to a datetime
SELECT id
FROM dbo.
WHERE TRY_PARSE(MANUFACTUREDDATE AS DATETIME) IS NULL;
-- find rows with a manufacture date older than 2 days
SELECT id
FROM dbo.
WHERE DATEDIFF(DAY, TRY_PARSE( MANUFACTUREDDATE AS DATETIME), GETDATE()) > 2;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
It is showing try_parse is not a recognized built-in function
-
TRY_PARSE was introduced in SQL Server 2012. You posted in a SQL Server 2012 forum so...
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
ramana3327 (10/5/2014)
It is showing try_parse is not a recognized built-in functionWhat is the output of
SELECT @@VERSIONon your system?
😎
-
It gave 2005. Sorry wrong place. I will post in the right place
-
ramana3327 (10/7/2014)
It gave 2005. Sorry wrong place. I will post in the right placeNah... no need now. Keep it here so that we don't lose any of the suggestions so far that might help people.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Jeff Moden (10/7/2014)
ramana3327 (10/7/2014)
It gave 2005. Sorry wrong place. I will post in the right placeNah... no need now. Keep it here so that we don't lose any of the suggestions so far that might help people.
I guess it's too late for that.
http://qa.sqlservercentral.com/Forums/Topic1623609-149-1.aspx
-
Eirikur Eiriksson (10/3/2014)
The following is wrongdatediff(day, convert(varchar,MANUFACTUREDDATE, 101),convert(varchar,getdate(), 103))
,it is comparing MM/DD/YYYY to DD/MM/YYYY, the only thing the convert does in the query is setting the format of the output string, which then are implicitly cast to datetime values.
😎
It's wrong for a far worse reason even than that. The first conversion should be to date or datetime, not to "varchar". The second parameter does not need converted and should not be.
If you have all valid date values in your table, then this should work:
datediff(day, convert(datetime, MANUFACTUREDDATE, 101), getdate())
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
-
Hi,
After so many trials with some one help, I got the result. It will help to some one else. So I am posting that here
DECLARE @Update VARCHAR(10)
SET @Update = convert(VARCHAR ,DATEADD(YEAR,-2,getdate()),3)
select @update
select
ID, convert(datetime, substring(replace(MANUFACTURED_DATE,'/',''),5,2) +
substring(replace(MANUFACTURED_DATE,'/',''),3,2) + substring(replace(MANUFACTURED_DATE,'/',''),1,2),112)
from dbo.Test
where
convert(datetime, substring(replace(MANUFACTURED_DATE,'/',''),5,2) +
substring(replace(MANUFACTURED_DATE,'/',''),3,2) + substring(replace(MANUFACTURED_DATE,'/',''),1,2),112) < @update
order by 2
The table stored the manufactureddate in dd/mm/yy (Varchar(30)) format
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply