DATETIME Format Codes - 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

General SQL Server Forums

New to SQL Server Programming

DATETIME Format Codes

Author

Topic

sampcuk
Starting Member

32 Posts

Posted - 2008-06-30 : 03:49:58

I want convert the date shown to the show the full month i.e. 31st December 2006. At the moment I am using code ,106 which gives 31st Dec 2006. I cant seem to find the code for the full month.
Any ideas?

Thanks
Sam

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-30 : 03:57:09

declare @date datetime

select @date = '20061231'

select replace(convert(varchar(30), @date, 106), left(datename(month, @date), 3), datename(month, @date))

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-30 : 03:59:33

If possible try to do this at your front end. Always take care of this type of formatting issues in your application.However, you can do it like this in SQL

SELECT DATENAME(dd,datecol) + ' ' + DATENAME(mm,datecol) + ' '+DATENAME(yy,datecol) FROM Table

sampcuk
Starting Member

32 Posts

Posted - 2008-06-30 : 04:21:59

I have tried both of these and the SQL is showing errors as column names do not exist etc... I am trying to change the DATE_LEFTDATE and APPPOINTED dates....

select EMPLOYEE.SURNAME, EMPLOYEE.FORENAME, EMPLOYEE.TITLE, EMPLOYEE.INITIALS, EMPLOYEE.KNOWN_AS,
EMPLOYEE.DEPARTMENT, EMPLOYEE.DIVISION, EMPLOYEE.LOCATION,
EMPLOYEE.GRADE, EMPLOYEE.COSTCENTRE, DATE_LEFTDATE=CONVERT (char(12), employee.date_left, 106), APPOINTEDDATE=CONVERT (char(12), employee.appointed, 106),
SV_LATEST_JOB.JOB_REF,SV_LATEST_JOB.JOB_NAME,
EMPLOYEE.ADDRESS1, EMPLOYEE.ADDRESS2, EMPLOYEE.TOWN, EMPLOYEE.COUNTY,
EMPLOYEE.POSTCODE, EMPLOYEE.EXT_EMAIL, EMPLOYEE.E_MAIL_ID, EMPLOYEE.BIRTHDATE,
MANAGER.FORENAME, MANAGER.SURNAME, V_DEPT.DESCRIPTION
from
EMPLOYEE,
SV_LATEST_JOB,
V_DEPT,
EMPLOYEE MANAGER
where
EMPLOYEE.EMPLOY_REF = :RECIPIENT_REF
and
EMPLOYEE.EMPLOY_REF = SV_LATEST_JOB.EMPLOY_REF
and
EMPLOYEE.MGR_REF *= MANAGER.EMPLOY_REF
and
EMPLOYEE.DEPARTMENT *= V_DEPT.CODE

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-30 : 04:24:47

is this the exact query that you used that give "showing errors as column names do not exist" error ?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-30 : 04:26:02

use DATENAME(dd,employee.date_left) + ' ' + DATENAME(mm,employee.date_left) + ' '+DATENAME(yy,employee.date_left) instead of

CONVERT (char(12), employee.date_left, 106)

similarly for other field. Also try to use ANSI join syntaxes as it improves readability. Also they are not supported from SQL 2005 onwards.

sampcuk
Starting Member

32 Posts

Posted - 2008-06-30 : 04:39:19

Thanks for this but how do I get the system to recognise the date_left field as it is not picking this up when I run the script. It says a merge field exists (as i am creating a letter in my database) but that no data source exists.

select EMPLOYEE.SURNAME, EMPLOYEE.FORENAME, EMPLOYEE.TITLE, EMPLOYEE.INITIALS, EMPLOYEE.KNOWN_AS,
EMPLOYEE.DEPARTMENT, EMPLOYEE.DIVISION, EMPLOYEE.LOCATION,
EMPLOYEE.GRADE, EMPLOYEE.COSTCENTRE, DATENAME(dd,employee.date_left) + ' ' + DATENAME(mm,employee.date_left) + ' '+DATENAME(yy,employee.date_left)
, APPOINTEDDATE=CONVERT (char(12), employee.appointed, 106),
SV_LATEST_JOB.JOB_REF,SV_LATEST_JOB.JOB_NAME,
EMPLOYEE.ADDRESS1, EMPLOYEE.ADDRESS2, EMPLOYEE.TOWN, EMPLOYEE.COUNTY,
EMPLOYEE.POSTCODE, EMPLOYEE.EXT_EMAIL, EMPLOYEE.E_MAIL_ID, EMPLOYEE.BIRTHDATE,
MANAGER.FORENAME, MANAGER.SURNAME, V_DEPT.DESCRIPTION
from
EMPLOYEE,
SV_LATEST_JOB,
V_DEPT,
EMPLOYEE MANAGER
where
EMPLOYEE.EMPLOY_REF = :RECIPIENT_REF
and
EMPLOYEE.EMPLOY_REF = SV_LATEST_JOB.EMPLOY_REF
and
EMPLOYEE.MGR_REF *= MANAGER.EMPLOY_REF
and
EMPLOYEE.DEPARTMENT *= V_DEPT.CODE

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-30 : 04:58:44

Why dont you have date_left field in db? or are you refering this by alias name outside? if yes, provide alias using as

DATENAME(dd,employee.date_left) + ' ' + DATENAME(mm,employee.date_left) + ' '+DATENAME(yy,employee.date_left) AS DATE_LEFTDATE

sampcuk
Starting Member

32 Posts

Posted - 2008-06-30 : 05:02:24

I do have it in the database it is just not recognising it when I use this instruction...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-30 : 05:04:54

quote:
Originally posted by sampcuk

I do have it in the database it is just not recognising it when I use this instruction...

Is this your full query or you have some other query added to this?

sampcuk
Starting Member

32 Posts

Posted - 2008-06-30 : 05:06:23

this is the full query

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-30 : 05:12:24

please also post the exact error message

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

sampcuk
Starting Member

32 Posts

Posted - 2008-06-30 : 05:19:57

Its OK have fixed it!!! Thanks so much for your help

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-30 : 05:23:58

quote:
Originally posted by sampcuk

Its OK have fixed it!!! Thanks so much for your help

what fixes it ?

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

sampcuk
Starting Member

32 Posts

Posted - 2008-06-30 : 05:27:08

There was a space between two words which is why it wasnt recognising the column name. This forum is great, really helpful..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-30 : 05:28:46

quote:
Originally posted by sampcuk

There was a space between two words which is why it wasnt recognising the column name. This forum is great, really helpful..

Thanks for letting us know.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources