I want to return as empty string and cast as datet

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 2000 Forums

Transact-SQL (2000)

I want to return as empty string and cast as datet

Author

Topic

svgeorge
Starting Member

23 Posts

Posted - 2007-11-20 : 14:10:29

I want to cast certain dates as datetime where there is a date value
and where there is e

mpty string i want to return as empty string.

now when i cast the whole field of my view i get the empty string value as 1/1/1900 12:00:00 AM which i do not want but empty.

I have uesd the queruy as below but does not work

please help.

CASE WHEN dbo.V_New_and_Carryover_Enrollments_AIMS_TEMP.DTE_PROJ_END = ' ' THEN dbo.V_New_and_Carryover_Enrollments_AIMS_TEMP.DTE_PROJ_END
ELSE cast(dbo.V_New_and_Carryover_Enrollments_AIMS_TEMP.DTE_PROJ_END AS datetime) AS DTE_PROJ_END

Please help as ASAP.

Thanks,

Santosh

georgev
Posting Yak Master

122 Posts

Posted - 2007-11-20 : 14:41:19

Any returned column can be of only ONE datatype.
Have you considered the use of NULL?

FYI an empty string is equal to the integer value of zero and zero is SQL Server is equivalent to the base date of 1900-01-01 00:00:00.000... Technically ;)

George
<3Engaged!

svgeorge
Starting Member

23 Posts

Posted - 2007-11-20 : 14:44:39

No I haven't considered using null how do I do it

I it something like this
, ISNULL(CONVERT(varchar(8), dbo.V_New_and_Carryover_Enrollments_AIMS_TEMP.DTE_PROJ_END, 101), ' ') AS DTE_PROJ_END

Thanks

George

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-21 : 01:25:18

You cant show it as empty string along with date values. Either return NULL or display empty string if you use front end

Madhivanan

Failing to plan is Planning to fail

georgev
Posting Yak Master

122 Posts

Posted - 2007-11-21 : 03:42:29

Try
[CODE]
Convert(datetime, NullIf(yourField, ''))
[/CODE]

George
<3Engaged!

svgeorge
Starting Member

23 Posts

Posted - 2007-11-21 : 08:59:34

Thanks George

IT worked I did the following
CONVERT(datetime, NULLIF (dbo.V_New_and_Carryover_Enrollments_AIMS_TEMP.DTE_PROJ_END, N'')) AS DTE_PROJ_END

Thanks again
George

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources