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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 formatting a date from a table in sql query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

barnsley
Starting Member

31 Posts

Posted - 05/07/2013 :  11:43:09  Show Profile  Reply with Quote
I would like to format a date in a table and insert it into a view.

here is a snippet of my select command which contains the date:

Select
UserDefinedData.FieldValue AS DOB
FROM         UserDefinedRows

The DOB (date of birth) field currently looks like this:
16031980

Is it possible to convert it to something like this:
16/03/1980

I tried this:
CONVERT(varchar(10), UserDefinedData.FieldValue, 103) AS DOB,

but it had no effect.

thanks.

mark.

Edited by - barnsley on 05/08/2013 04:09:29

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 05/07/2013 :  12:37:34  Show Profile  Reply with Quote
what do you mean by inserting it into a view? View is simply a select query - you can only insert into a table.

Also, its better to use "date" datatype for a date field.. not string/varchar. As far formatting goes, if this is required for display purposes in an application/report, you can do it on front end.

Cheers
MIK
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/08/2013 :  00:31:47  Show Profile  Reply with Quote
you need to do logic like below provided the format of values is consistent

SELECT     STUFF(STUFF(UserDefinedData.FieldValue,3,0,'/'),6,0,'/') AS DOB
..


Also I second MIKs suggestion. you should always chose proper datatype for your fields

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

barnsley
Starting Member

31 Posts

Posted - 05/08/2013 :  04:50:14  Show Profile  Reply with Quote
thanks for the replies.
Perhaps I shouldn't have said insert it into a view, if the SELECT query works, then I guess it doesn't matter how I use it.

The datatype of this field is ntext (its a DotNetNuke CMS which collates all data from various fields and inserts them into 1 field called UserDefinedData. I suppose I could/should have used a calendar feature for users to select when inserting the data, but the calendar has no drop down box for year and you have to click on previous years many times hence the reason for a simple text field).





mark.
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 05/08/2013 :  07:01:44  Show Profile  Reply with Quote
quote:
Originally posted by barnsley

thanks for the replies.
Perhaps I shouldn't have said insert it into a view, if the SELECT query works, then I guess it doesn't matter how I use it.

The datatype of this field is ntext (its a DotNetNuke CMS which collates all data from various fields and inserts them into 1 field called UserDefinedData. I suppose I could/should have used a calendar feature for users to select when inserting the data, but the calendar has no drop down box for year and you have to click on previous years many times hence the reason for a simple text field).



mark.


Fine, but even in the case of "text box "its better to have the field as Date datatype in the database. The option you could have is to put some sort of constraint on that text box such that anything entered is a valid date (if could be done) e.g. 19031980, not 19198003. If not it would lead to sort of garbage data. Even better, instead of one text box, there should have been three text boxes for Day, Month and Year respectively. And upon saving/updating a record, that text value(s) (of either the three or one text box) are manipulated into a proper date format and then pass it to the StoredProcedure/Insert/Update statment.

Cheers
MIK

Edited by - MIK_2008 on 05/08/2013 07:03:02
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.06 seconds. Powered By: Snitz Forums 2000