Issue with Date column in View

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)

Issue with Date column in View

Author

Topic

ssaapk
Starting Member

4 Posts

Posted - 2014-11-11 : 15:00:19

I have the following view
create view test_vw
as
select customer_id,
ponumber,
GetpoDate(ponumber) as podate
from customer

customer id is varchar
ponumber is numeric
GetpoDate is a function , which converts numeric to datetime
i see the data type as datetime for the column podate in the view

i am not able to run any query from the view with podate in the where
clause.

select * from text_vw
where CONVERT(date,podate,101) >= '09/01/14'

getting error Conversion failed when converting date and/or time from character string when I use podate in the where clause.

any body has any idea what the problem is

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-11 : 15:02:05

Not sure why you added the convert, just do this:
select *
from text_vw
where podate >= '09/01/14'

Does GetpoDate output a date column?

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

ssaapk
Starting Member

4 Posts

Posted - 2014-11-11 : 15:14:08

I get the same error "Conversion failed when converting date and/or time from character string" when i use
select *
from text_vw
where podate >= '09/01/14'

The function returns datetime. Here is the script for the function.

create FUNCTION [dbo].[GetpoDate]
(
@ip_po_no AS NUMERIC
)
RETURNS DATETIME
AS
BEGIN

DECLARE
@lv_po_no AS VARCHAR(25),
@po_date AS VARCHAR(25),
@ip_date AS DATETIME

if LEN(@ip_po_no) < 8
BEGIN
SET @lv_po_no = '0'+convert(varchar,@ip_po_no)
END
else
BEGIN
SET @lv_po_no = convert(varchar,@ip_po_no)
END

set @po_date = left(@lv_po_no,2) + '/' + substring(@lv_po_no,3,2) + '/' + right(@lv_po_no,4)
set ip_date = CONVERT(datetime,@po_date,101)

RETURN @ip_date
END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-11 : 15:17:44

I wonder if your dateformat setting is impacting it. Try adding SET DATEFORMAT mdy.

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

ssaapk
Starting Member

4 Posts

Posted - 2014-11-11 : 15:41:22

SET DATEFORMAT mdy dis not fix the issue.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-11 : 15:43:49

The issue is with the GetpoDate function then. It is not creating datetime output for all rows. You'll need to debug the function.

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

ssaapk
Starting Member

4 Posts

Posted - 2014-11-11 : 16:39:16

yes. You are right. Some of the ponumber column was having value as 1 and 99999999.Thank you again for your help.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-11 : 17:04:31

quote:
Originally posted by ssaapk

yes. You are right. Some of the ponumber column was having value as 1 and 99999999.Thank you again for your help.

Glad to help and glad you found the issue.

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

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-11-11 : 17:05:57

You'll want to write functions as efficiently as possible to improve performance, particularly getting rid of unrequired local variables.


CREATE FUNCTION [dbo].[GetpoDate] 
	(
	@ip_po_no AS numeric
	)
RETURNS DATETIME
AS
 BEGIN
 RETURN (
     SELECT STUFF(STUFF(lv_po_no, 3, 0, '/'), 6, 0, '/')
     FROM (
         SELECT @ip_po_no as ip_po_no
     ) AS param_values
     CROSS APPLY (
         SELECT RIGHT('0' + CONVERT(varchar(8), ip_po_no), 8) AS lv_po_no
     ) AS assign_alias_names
 )
 END --FUNCTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources