SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Converting date and/or time from Character string
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

archana23
Yak Posting Veteran

77 Posts

Posted - 10/22/2013 :  14:07:13  Show Profile  Reply with Quote
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

USA
37133 Posts

Posted - 10/22/2013 :  14:11:41  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3707 Posts

Posted - 10/22/2013 :  14:13:21  Show Profile  Reply with Quote
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)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/22/2013 :  14:31:09  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3707 Posts

Posted - 10/22/2013 :  14:36:16  Show Profile  Reply with Quote
And also see this: http://myshallowsqlblog.wordpress.com/sql-dates-use-iso-8601-format/
Go to Top of Page

archana23
Yak Posting Veteran

77 Posts

Posted - 10/22/2013 :  15:22:04  Show Profile  Reply with Quote
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
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
964 Posts

Posted - 10/22/2013 :  21:48:08  Show Profile  Reply with Quote

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

SELECT FORMAT(@FROMDATE, 'yyyyMMdd'), FORMAT(@THRUDATE, 'yyyyMMdd')
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/23/2013 :  01:34:57  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 10/24/2013 :  03:54:16  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000