Date question

[sillysod]

Hello,

Im working with the SQL database for our accounts package at work

Dates in the system are stored as Integer values and in the following format

20050804

I would like to create a view which replicates the table in question but replaces all the date fields with a proper date field

I know im going to have to use a user def function of some kind but im at a loss where to start and considering the number of ledger transactions would like the process to be as efficient as possible.

Having never done any sort of programming in SQL i would be gratefull for any help on what needs to be in the function and how to actually call it from my view

 

[genomon]

Check out CAST & CONVERT in BOL!

The Microsoft mascot is a butterfly.
A butterfly is a bug.
Think about it....

 

[SQLBill]

BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine
or online at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/startsql/portal_7ap1.asp

Also, your question has been brought up lots of times. Check the FAQs and do a search in this forum.

-SQLBill

Posting advice: FAQ481-4875

 

[SajidAttar]

sillysod,

Use the following convert function in your view for the date field which you want to show in proper date format

convert(varchar,convert(datetime,FieldName),106) as FieldName

something like this

create view myView as
select Col1, Col2, Col3, Col4,
convert(varchar,convert(datetime,[Red]Col5[/Red]),106) as Col5
from myTable where . . .

it will return the value of your columns in dd mmm yyyy (05 Aug 2005)
if you want different date formats change the firde parameter of first convert function accordingly ([blue]in this case it is 106 [/blue])


Regards,


"There are no secrets to success. It is the result of preparation, hard work, and learning from failure." -- Colin Powell

 

[sillysod]

Thanks

I tried the above but it says arithmetic overflow when converting to date time

I have played with converting the type, but i cant understand where i tell SQL that its in the format yyyymmdd ?

As i understand your post "106" refers to how the date will output ?

 

[SQLBill]

Before your SELECT use:

SET DATEFORMAT YMD

That tell SQL Server the inputted date value is in that format.

-SQLBill

Posting advice: FAQ481-4875

 

[sillysod]

Im using the below sql to test it but it fails saying
incorrect syntax near the word SET

CREATE VIEW udefLedger AS
SET DATEFORMAT AS YMD
SELECT CONVERT(varchar, CONVERT(datetime, TransDate), 106) AS NewDate, AccCode, TransDate, Ammount
FROM dbo.LEDGER

 

[SQLBill]

Why are you using AS in the SET DATEFORMAT line?

-SQLBill

Posting advice: FAQ481-4875

 

[sillysod]

CREATE VIEW udefLedger AS
SET DATEFORMAT YMD
SELECT
ACCNT_CODE, TRANSDATE, AMOUNT, CONVERT(varchar, CONVERT(datetime, TransDate), 106)
FROM dbo.LEDGER

Oops But it still gives me incorrect syntaxt near SET

 

[SQLBill]

Maybe VIEWs don't accept the SET DATEFORMAT, but if so I can't find that anywhere.

-SQLBill


Posting advice: FAQ481-4875

 

[vongrunt]

yyyymmdd is unseparated ISO format always recognized by SQL Server (no need for SET DATEFORMAT|LANGUAGE). So simple conversion to varchar(8) first and then datetime should work. For example:

SELECT convert(datetime, convert(varchar(8), 20050804))

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)