Separte date from time - 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 2005 Forums

Transact-SQL (2005)

Separte date from time

Author

Topic

blast
Starting Member

9 Posts

Posted - 2007-01-25 : 12:09:53

hi
i need help in separating the date from time, please help thanks in advance
my query is like
select dbo.udf_getDateString(o_date,GETUTCDATE(),GETDATE()) from table a

i get out put like
11/22/2006 12:41PM

i need only
11/22/2006

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-25 : 12:14:12

Use the CONVERT function to convert into many different formats, for example

SELECT CONVERT(varchar(10), getdate(), 101)

blast
Starting Member

9 Posts

Posted - 2007-01-25 : 12:50:24

thank you so m uch for your help it worked.

I would be glad if you help me in silliar issue please! the issue is
select (dbo.udf_convertDate2UTC(c.last_mod_dt,GETUTCDATE(),GETDATE()))"Mod Date" from table c

i get output like
2007-01-04 07:45:20.000

the outout is perrfect but when i take it to cystal it shows me that it is date type is date time , i wanted in same format but to be in String data type can u help.....thanks inadvance

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-25 : 13:10:41

So just do what I showed you before but use a different date style (see CONVERT in Books Online for all the styles), the one you want is 121

select CONVERT(varchar(23), dbo.udf_convertDate2UTC(c.last_mod_dt,GETUTCDATE(),GETDATE()), 121) [Mod Date] from table c

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-25 : 13:13:46

One more thing - I'd recommend that you don't use the CONVERT function to convert into a string. Rather pass the date to Crystal and then convert it as necessary there, otherwise you will later find that when you want to use sorting and filtering and so on in Crystal it isn't going to work because you'll be sorting and filtering on a string instead of a date.

jogin malathi
Posting Yak Master

117 Posts

Posted - 2007-02-06 : 07:04:30

--------------------------------------------------------------------------------

i get out put like
11/22/2006 12:41PM

i need only
12:41PM

Malathi Rao

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-06 : 07:27:23

Have you try the method snSQL posted ? Why don't you start a new thread ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-06 : 07:50:08

SELECT LTRIM(RIGHT(CONVERT(VARCHAR, GETDATE(), 0), 7))

Peter Larsson
Helsingborg, Sweden

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-02-06 : 08:44:10

Have you tried like Sn suggested and let Crystal Reports have the date untouched? Crystal is a VERY powerful reporting tool, and will format the date just the way you want it. You simply have to search the help file that comes with Crystal Reports to find out how.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources