Converting date and/or time from Character string

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

New to SQL Server Programming

Converting date and/or time from Character string

Author

Topic

archana23
Yak Posting Veteran

89 Posts

Posted - 2013-10-22 : 14:07:13

Hi,

My Query is as shown below

Declare @FROMDATE DATETIME
Declare @THRUDATE DATETIME
Declare @Parm Varchar(250)
set @FROMDATE = '09/25/2013'
set @THRUDATE = '09/28/2013'
set @Parm = 'FROMDATE=' + @FROMDATE + ';THRUDATE=' + @THRUDATE

I am getting an error at above line saying that

Conversion failed when converting date and/or time from character string.

How can i overcome this problem?

Archana

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-10-22 : 14:11:41

Use varchar data type for the two date parameters. Otherwise, you'll have to add a CAST or CONVERT function to them so that you can concatenate them in @parm.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-22 : 14:13:21

You can get around the conversion problem using the following. However, if you are planning to use @Parm as a parameter to something, this would not be the best way to do that. Keep dates as datetime types and pass those to where you are going to consume it. Also, if you are going to use @Parm in a SQL query, you would need additional single quotes around the dates, and the semi-colon is not required - instead you need an "AND".

Declare @FROMDATE DATETIME
Declare @THRUDATE DATETIME
Declare @Parm Varchar(250)
set @FROMDATE = '09/25/2013'
set @THRUDATE = '09/28/2013'
set @Parm = 'FROMDATE=' + CONVERT(VARCHAR(10), @FROMDATE, 101) + 
	';THRUDATE=' +   CONVERT(VARCHAR(10), @THRUDATE, 101)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-22 : 14:31:09

quote:
Originally posted by archana23

Hi,

My Query is as shown below

Declare @FROMDATE DATETIME
Declare @THRUDATE DATETIME
Declare @Parm Varchar(250)
set @FROMDATE = '09/25/2013'
set @THRUDATE = '09/28/2013'
set @Parm = 'FROMDATE=' + @FROMDATE + ';THRUDATE=' + @THRUDATE

I am getting an error at above line saying that

Conversion failed when converting date and/or time from character string.

How can i overcome this problem?

Archana

see

http://visakhm.blogspot.in/2011/12/why-iso-format-is-recommended-while.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-22 : 14:36:16

And also see this: http://myshallowsqlblog.wordpress.com/sql-dates-use-iso-8601-format/

archana23
Yak Posting Veteran

89 Posts

Posted - 2013-10-22 : 15:22:04

Thank you all for your replies. i got it.

i have one more question , My @ROMDATE and @HRUDATE are in like this

@FROMDATE = '09/25/2013'
@THRUDATE = '09/28/2013'

these values are coming from input paramets of my stored procedure i need to convert these dates as

@FROMDATE = '20130925'
@THRUDATE = '20130928'

inside my stored procedure . so how can i do this?

Thank you.

Archana

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-10-22 : 21:48:08

[code]
Declare @FROMDATE DATETIME
Declare @THRUDATE DATETIME
set @FROMDATE = '09/25/2013'
set @THRUDATE = '09/28/2013'

SELECT FORMAT(@FROMDATE, 'yyyyMMdd'), FORMAT(@THRUDATE, 'yyyyMMdd')[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-23 : 01:34:57

quote:

Originally posted by waterduck


Declare @FROMDATE DATETIME
Declare @THRUDATE DATETIME
set @FROMDATE = '09/25/2013'
set @THRUDATE = '09/28/2013'

SELECT FORMAT(@FROMDATE, 'yyyyMMdd'), FORMAT(@THRUDATE, 'yyyyMMdd')

Will work only from SQL 2012 onwards

FOr previous versions use CONVERT


SELECT CONVERT(varchar(10),@Value,112)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-10-24 : 03:54:16

Why are you using @param? Are you using them in WHERE clause? The correct method is

....
WHERE
FROMDATE=@FROMDATE and THRUDATE=@THRUDATE

and if you want to handle time part too

....
WHERE
FROMDATE>=@FROMDATE and THRUDATE<dateadd(day,1,@THRUDATE)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources