SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Convert date
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 07/25/2008 :  09:17:34  Show Profile  Reply with Quote
Right now I have a date of 2008-01-01, I want it to say 01/01/2008 how can I convert that? This is a datetime, null field

khtan
In (Som, Ni, Yak)

Singapore
17586 Posts

Posted - 07/25/2008 :  09:19:51  Show Profile  Reply with Quote
do it it front end or in T-SQL use convert()


KH
Time is always against us

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 07/25/2008 :  09:20:45  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by werhardt

Right now I have a date of 2008-01-01, I want it to say 01/01/2008 how can I convert that? This is a datetime, null field


Where do you want to show the converted dates?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

VGuyz
Posting Yak Master

121 Posts

Posted - 07/25/2008 :  09:20:59  Show Profile  Reply with Quote
Check this


SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]
Go to Top of Page

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 07/25/2008 :  09:50:36  Show Profile  Reply with Quote
This field has dates already in the report coming out as 2008-01-01 I just need my report to come out with saying 01/01/2008. Can I do somekind of replace?
replace(pro_eff,'/','')...something.

quote:
Originally posted by VGuyz

Check this


SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17586 Posts

Posted - 07/25/2008 :  09:54:17  Show Profile  Reply with Quote
datetime is not stored in the database in any particular format but in an internal representation. what you see as 2008-01-01 is just how Query Analyzer or SSMS present the datetime to you.

You should be formatting the datetime value in to the format you want in your front end application. If you have to do it in SQL, then use the convert() function. Please check out the Books Online for more details on convert()


KH
Time is always against us

Go to Top of Page

VGuyz
Posting Yak Master

121 Posts

Posted - 07/25/2008 :  10:15:39  Show Profile  Reply with Quote
HI,
Check with this,

select convert(varchar(15),convert(datetime ,('2008-01-01')),101)
Go to Top of Page

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 07/25/2008 :  11:34:34  Show Profile  Reply with Quote
Thanks for your help.

quote:
Originally posted by VGuyz

HI,
Check with this,

select convert(varchar(15),convert(datetime ,('2008-01-01')),101)


Go to Top of Page

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 08/11/2008 :  11:16:54  Show Profile  Reply with Quote
I have a field that is a datetime field, but I needed the format to look like 01/01/2008. The formula that I had worked, see below

[Termination Date] =
Case CONVERT(VARCHAR(10), (dgh.prop_trm), 101)
WHEN '03/23/1977' THEN ''
else CONVERT(VARCHAR(10), (dgh.prop_trm), 101)
End,

when I wanted to use it as a date range it won't read it as a date.

How can I fix this?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 08/11/2008 :  11:33:33  Show Profile  Reply with Quote
quote:
Originally posted by werhardt

I have a field that is a datetime field, but I needed the format to look like 01/01/2008. The formula that I had worked, see below

[Termination Date] =
Case CONVERT(VARCHAR(10), (dgh.prop_trm), 101)
WHEN '03/23/1977' THEN ''
else CONVERT(VARCHAR(10), (dgh.prop_trm), 101)
End,

when I wanted to use it as a date range it won't read it as a date.

How can I fix this?


why are converting it to varchar in first place? whats the significance of 23/03/1977? is it your default date value?
Go to Top of Page

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 08/11/2008 :  11:57:34  Show Profile  Reply with Quote
my boss wants it to have a better format. The 1977 means that here is nothing in that field. It's the way our one systm works.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 08/11/2008 :  12:00:15  Show Profile  Reply with Quote
quote:
Originally posted by werhardt

my boss wants it to have a better format. The 1977 means that here is nothing in that field. It's the way our one systm works.


The formatting is a presentation issue which needs to be dealt with at your presentation layer which is your front end application.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 08/11/2008 :  12:01:24  Show Profile  Visit SwePeso's Homepage  Reply with Quote
SELECT	CONVERT(VARCHAR(10), NULLIF(dgh.prop_trm, '03/23/1977'), 101) AS [Termination Date]
FROM	YourTable



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000