convert varchar into date format in sql server2000

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

convert varchar into date format in sql server2000

Author

Topic

naidu4u
Starting Member

23 Posts

Posted - 2010-10-05 : 22:23:00

I need to convert a date of type varchar into date format but I dont need the time part in it and I am using SQL server 2000. I've tried using date function inorder to extract the date part but it dint work in SQL 2000. So, please help me in getting just the date into datetime format.

select convert(varchar(10), a.dates, 101) dates from
(select getdate()-5 dates
union
select getdate()-4
union
select getdate()-3
union
select getdate()-2
union
select getdate()-1
union
select getdate()
union
select getdate()+1
union
select getdate()+2
union
select getdate()+3
union
select getdate()+4
union
select getdate()+5) a

my output looks like
dates:
09/30/2010
10/01/2010
10/02/2010
10/03/2010
10/04/2010
10/05/2010
10/06/2010
10/07/2010
10/08/2010
10/09/2010
10/10/2010

this is my query but the result which am getting is in varchar format but I need the same date in date format but with out time. I need to use this sql in my cognos report and I cant use any UDF or stored procedures

Thanks in advance.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-10-05 : 23:00:48

I think you may be confusing "format" with "datatype". Sql server 2000 does not have a date datatype that doesn't include a time portion. There are two choices: datetime and smalldatetime. Anytime you see a date representation on screen it is a string representation of a date. Even "select getdate()" results to a the current datetime displayed on your screen in whatever "format" windows is configured to show dates.

So having said that are you looking for a way to return all your dates as datetime 12:00 AM? ie: "9/30/2010 00:00:00.000" ? If so you can change your select to:
select dateadd(day, datediff(day, 0, a.dates), 0)

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-10-06 : 05:29:39

Where do you want to show formatted dates? If you use front end application, do formation there

Madhivanan

Failing to plan is Planning to fail

naidu4u
Starting Member

23 Posts

Posted - 2010-10-06 : 10:07:08

select convert(datetime, convert(varchar(10), a.dates, 101),120) dates from
(select getdate()-5 dates
union
select getdate()-4
union
select getdate()-3
union
select getdate()-2
union
select getdate()-1
union
select getdate()
union
select getdate()+1
union
select getdate()+2
union
select getdate()+3
union
select getdate()+4
union
select getdate()+5) a

I have tried this and this is giving me date how I wanted and time as 00:00:00.000.

naidu4u
Starting Member

23 Posts

Posted - 2010-10-06 : 10:08:06

quote:
Originally posted by madhivanan

Where do you want to show formatted dates? If you use front end application, do formation there

Madhivanan

Failing to plan is Planning to fail

The problem is am using Cognos and there it is not letting me to convert the datatype...

Thanks

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-06 : 10:28:38

quote:
Originally posted by naidu4u

I have tried this and this is giving me date how I wanted and time as 00:00:00.000.

Hi Naidu,

AFAIK you cannot remove the timeportion from datetime datatype.
The only way to remove them is to convert it to varchar and you cannot do that due to some limitation in cognos.

I don't have any knowledge on cognos. I googled for you and found one link which says that you can convert varchar to date and vice-versa in cognos.

http://publib.boulder.ibm.com/infocenter/cnow/v4r6m0/index.jsp?topic=/com.ibm.swg.im.cognos.sbi.4.6.0.doc/sbi_id13053NA0787.html

Regards,
Bohra

naidu4u
Starting Member

23 Posts

Posted - 2010-10-06 : 10:46:39

quote:
Originally posted by pk_bohra

quote:
Originally posted by naidu4u

I have tried this and this is giving me date how I wanted and time as 00:00:00.000.

Hi Naidu,

AFAIK you cannot remove the timeportion from datetime datatype.
The only way to remove them is to convert it to varchar and you cannot do that due to some limitation in cognos.

I don't have any knowledge on cognos. I googled for you and found one link which says that you can convert varchar to date and vice-versa in cognos.

http://publib.boulder.ibm.com/infocenter/cnow/v4r6m0/index.jsp?topic=/com.ibm.swg.im.cognos.sbi.4.6.0.doc/sbi_id13053NA0787.html

Regards,
Bohra

Thanks a lot Bohra, I will look into that and try to find something...
appreciate your help....

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources