Capture only date from entire datetime

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

SQL Server Development (2000)

Capture only date from entire datetime

Author

Topic

disha
Starting Member

34 Posts

Posted - 2007-11-27 : 21:40:40

Hi xperts,

I have a event date in the format as 22-01-2007 10:08:09 Am/pm

i want to extract only date ie 22-01-2007 .
event date type in database is varchar .
plz let me know how to do dis .

i tried following code but it didnt help

cast(convert(char(11),eventdate)as datetime) it gives me

dd-mm-yyyy 12:00:00
plz help ...

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-11-27 : 22:12:41

If you convert to the DATETIME datatype it will always contain the hours, mins and secs. Since your field is a VARCHAR datatype you can use SELECT SUBSTRING(eventdate,1,10) this will give you the format you desire but the field will still be VARCHAR, if you CAST it to DATETIME it will then add the hh:mm:ss as 00:00:00.

Future guru in the making.

disha
Starting Member

34 Posts

Posted - 2007-11-27 : 23:27:28

i just need the date .
i dont want time

can u plz provide me the code. or using substr il get it il try using substr .

plz help me if u have any specific code for this

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-11-28 : 00:22:08

If you use what I posted it will provide just the date portion, here again:
SELECT SUBSTRING(eventdate,1,10)

Future guru in the making.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-28 : 01:15:57

why don't you format the date in your front end application ?

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-28 : 01:44:39

quote:
Originally posted by disha

Hi xperts,

I have a event date in the format as 22-01-2007 10:08:09 Am/pm

i want to extract only date ie 22-01-2007 .
event date type in database is varchar .
plz let me know how to do dis .

i tried following code but it didnt help

cast(convert(char(11),eventdate)as datetime) it gives me

dd-mm-yyyy 12:00:00
plz help ...

1 Always use proper DATETIME datatype to store dates
2 Formation only matters whe you want to show dates somewhere
3 As said, if you want to show formatted dates in front end, then make use of format function there

Madhivanan

Failing to plan is Planning to fail

disha
Starting Member

34 Posts

Posted - 2007-11-28 : 02:20:45

thnx,
but it doesnt

the substr doesnt work
it still shows 12:00:00 when i fire a query in sql server

plz let me know how do i get dis done .

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-28 : 03:12:31

Did you read my previous reply?

Madhivanan

Failing to plan is Planning to fail

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-11-28 : 11:04:04

quote:
Originally posted by disha

thnx,
but it doesnt

the substr doesnt work
it still shows 12:00:00 when i fire a query in sql server

plz let me know how do i get dis done .

As the others posted you should do formatting in the front end. As far as the substring is concerned it works if you keep it as a VARCHAR datatype. As I already said if you cast or convert it to datetime it will put in the hh:mm:ss and there is nothing you can do about it.

Future guru in the making.

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-12-01 : 21:24:14

And, if there is no front end?

Disha, something is very wrong with your description of the problem. In order to clear it up, please post the table creation statement for the table that has the EventDate column in it and also run the following (of course, changing the table name) and post the results.

SELECT TOP 10 EventDate FROM [yourtable]

--Jeff Moden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources