formatting a date from a table in sql query

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)

formatting a date from a table in sql query

Author

Topic

barnsley
Starting Member

34 Posts

Posted - 2013-05-07 : 11:43:09

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.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-05-07 : 12:37:34

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-08 : 00:31:47

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

barnsley
Starting Member

34 Posts

Posted - 2013-05-08 : 04:50:14

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.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-05-08 : 07:01:44

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources