text to date - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

text to date

Author

Topic

mikebird
Aged Yak Warrior

529 Posts

Posted - 2010-02-25 : 11:38:38

How do I change a text date parameter to a smalldatetime? CAST or CONVERT?

'01-01-2010' to smalldatetime?

I get an error in RS report where conversion failed changing character string to smalldate time.

It started as a CONVERT(DATETIME, '2010-01-01 00:00:00', 102)

Now we need RS parameters are '01-01-2010' results of the parameter input.

Either use this function or just @Start_Date right in, get same error above...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-25 : 11:40:05

why are you using text? text is deprecated in sql 2008 so use varchar(max). you can use CONVERT to get it to datetime.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mikebird
Aged Yak Warrior

529 Posts

Posted - 2010-02-25 : 11:59:57

The reason is, Mr Knows Everything, in which I must be making the mistake here.... Reporting Services offer a handful of data types for it's parameters. I didn't choose text or whatever. I chose datetime. Running the simple start to the report, retrieving the parameter, it passes '01-01-2001' string! Can't find anything in the msdn table of conversion from text to datetime. I'm surprised.

Any ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-25 : 12:11:19

what? i'm asking about table datatype not report parameter type

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-02-25 : 13:55:48

I don't know whether your example 01-01-2001 is d-m-y or m-d-y, but all these work:


Explicit conversions:
SELECT CONVERT(datetime, '31-12-2001', 105)

SELECT CONVERT(datetime, '12-31-2001', 110)

or Implicit conversions with DATEFORMAT hint:

SET DATEFORMAT dmy

SELECT CONVERT(datetime, '31-12-2001')

SET DATEFORMAT mdy

SELECT CONVERT(datetime, '12-31-2001')

Kristen
Test

22859 Posts

Posted - 2010-02-25 : 14:17:35

P.S. If they are genuinely in TEXT datatype (rather than just "text string") then you will need to Cast to Varchar first:

SELECT CONVERT(datetime, CONVERT(varchar(30), MyTextObject), 105)

mikebird
Aged Yak Warrior

529 Posts

Posted - 2010-02-26 : 04:10:59

Thanks! All appreciated. The reason why we don't examine the datatype and swap it over is that it's a DWareHouse of Evil where truncating the entire schema and rebuilding the many billion terabytes, just for one column change of collation or something like that? And no write permissions, either - gotta be a locked-up, ongoing historical legacy system.

Maybe shoulda posted on DBA forum. There must be many of these scenarios? Old legacy should map to reporting?!?! Surely!

Kristen
Test

22859 Posts

Posted - 2010-02-26 : 08:19:03

Trouble is every time you do a CONVERT between types like Datetime and Varchar it steals CPU cycles. The CPU you save by changing the datatype of the column to DATETIME may be significant (can't promise that of course ...)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources