Conversion of varchar to datetime

Question

Conversion of varchar to datetime

ramana3327

SSCoach

Points: 19722
More actions
October 2, 2014 at 12:39 pm

#297061

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

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

roryp 96873 SSCertifiable Points: 6649 More actions · Answer 1

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).

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

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/

ramana3327 SSCoach Points: 19722 More actions · Answer 3

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?

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

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)

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 5

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.

😎

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 6

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 tempdb

IF 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 data

SELECT *

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

ramana3327 SSCoach Points: 19722 More actions · Answer 7

It is showing try_parse is not a recognized built-in function

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 8

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

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 9

ramana3327 (10/5/2014)
It is showing try_parse is not a recognized built-in function

What is the output of

SELECT @@VERSION

on your system?

😎

ramana3327 SSCoach Points: 19722 More actions · Answer 10

It gave 2005. Sorry wrong place. I will post in the right place

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 11

ramana3327 (10/7/2014)
It gave 2005. Sorry wrong place. I will post in the right place

Nah... 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)

Luis Cazares SSC Guru Points: 183706 More actions · Answer 12

Jeff Moden (10/7/2014)
ramana3327 (10/7/2014)
It gave 2005. Sorry wrong place. I will post in the right place
Nah... 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://www.sqlservercentral.com/Forums/Topic1623609-149-1.aspx

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ScottPletcher SSC Guru Points: 101103 More actions · Answer 13

Eirikur Eiriksson (10/3/2014)
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.
😎

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ramana3327 SSCoach Points: 19722 More actions · Answer 14

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