15,968,352 members
Home / Discussions / Database

# Database

 Re: Convert Decimal To Date? [modified] Pete O'Hanlon22-Jul-08 9:19 Pete O'Hanlon 22-Jul-08 9:19
 Re: Convert Decimal To Date? Rocky#22-Jul-08 18:24 Rocky# 22-Jul-08 18:24
 Re: Convert Decimal To Date? [modified] Pete O'Hanlon22-Jul-08 22:06 Pete O'Hanlon 22-Jul-08 22:06
 Re: Convert Decimal To Date? Rocky#22-Jul-08 23:20 Rocky# 22-Jul-08 23:20
 Re: Convert Decimal To Date? Pete O'Hanlon23-Jul-08 1:08 Pete O'Hanlon 23-Jul-08 1:08
 Re: Convert Decimal To Date? Rocky#23-Jul-08 3:02 Rocky# 23-Jul-08 3:02
 Re: Convert Decimal To Date? Blue_Boy21-Jul-08 0:36 Blue_Boy 21-Jul-08 0:36
 Re: Convert Decimal To Date? zahedonline21-Jul-08 0:37 zahedonline 21-Jul-08 0:37
 Hi obarahmeh, I have a solution for your question hope this helps you out What you can do is that create a custom function in your Database, Code given below Function: ********* ```CREATE FUNCTION dbo.FormatDateTime ( @dt DATETIME, @format VARCHAR(16) ) RETURNS VARCHAR(64) AS BEGIN DECLARE @dtVC VARCHAR(64) SELECT @dtVC = CASE @format WHEN 'LONGDATE' THEN DATENAME(dw, @dt) + ',' + SPACE(1) + DATENAME(m, @dt) + SPACE(1) + CAST(DAY(@dt) AS VARCHAR(2)) + ',' + SPACE(1) + CAST(YEAR(@dt) AS CHAR(4)) WHEN 'LONGDATEANDTIME' THEN DATENAME(dw, @dt) + ',' + SPACE(1) + DATENAME(m, @dt) + SPACE(1) + CAST(DAY(@dt) AS VARCHAR(2)) + ',' + SPACE(1) + CAST(YEAR(@dt) AS CHAR(4)) + SPACE(1) + RIGHT(CONVERT(CHAR(20), @dt - CONVERT(DATETIME, CONVERT(CHAR(8), @dt, 112)), 22), 11) WHEN 'SHORTDATE' THEN LEFT(CONVERT(CHAR(19), @dt, 0), 11) WHEN 'SHORTDATEANDTIME' THEN REPLACE(REPLACE(CONVERT(CHAR(19), @dt, 0), 'AM', ' AM'), 'PM', ' PM') WHEN 'UNIXTIMESTAMP' THEN CAST(DATEDIFF(SECOND, '19700101', @dt) AS VARCHAR(64)) WHEN 'YYYYMMDD' THEN CONVERT(CHAR(8), @dt, 112) WHEN 'YYYY-MM-DD' THEN CONVERT(CHAR(10), @dt, 23) WHEN 'YYMMDD' THEN CONVERT(VARCHAR(8), @dt, 12) WHEN 'YY-MM-DD' THEN STUFF(STUFF(CONVERT(VARCHAR(8), @dt, 12), 5, 0, '-'), 3, 0, '-') WHEN 'MMDDYY' THEN REPLACE(CONVERT(CHAR(8), @dt, 10), '-', SPACE(0)) WHEN 'MM-DD-YY' THEN CONVERT(CHAR(8), @dt, 10) WHEN 'MM/DD/YY' THEN CONVERT(CHAR(8), @dt, 1) WHEN 'MM/DD/YYYY' THEN CONVERT(CHAR(10), @dt, 101) WHEN 'DDMMYY' THEN REPLACE(CONVERT(CHAR(8), @dt, 3), '/', SPACE(0)) WHEN 'DD-MM-YY' THEN REPLACE(CONVERT(CHAR(8), @dt, 3), '/', '-') WHEN 'DD/MM/YY' THEN CONVERT(CHAR(8), @dt, 3) WHEN 'DD/MM/YYYY' THEN CONVERT(CHAR(10), @dt, 103) WHEN 'HH:MM:SS 24' THEN CONVERT(CHAR(8), @dt, 8) WHEN 'HH:MM 24' THEN LEFT(CONVERT(VARCHAR(8), @dt, 8), 5) WHEN 'HH:MM:SS 12' THEN LTRIM(RIGHT(CONVERT(VARCHAR(20), @dt, 22), 11)) WHEN 'HH:MM 12' THEN LTRIM(SUBSTRING(CONVERT( VARCHAR(20), @dt, 22), 10, 5) + RIGHT(CONVERT(VARCHAR(20), @dt, 22), 3)) ELSE 'Invalid format specified' END RETURN @dtVC END GO``` Demo on Usage(Taking your own question as a sample): ***************************************************** ```select dbo.FormatDateTime(convert(datetime, cast(DayDate AS char(8))),'YYYY-MM-DD') AS Date from TableName where DayDate = 20080721``` OutPut: ******* 2008-07-21 Usage of Function: ****************** ```DECLARE @now DATETIME SET @now = GETDATE() PRINT dbo.FormatDateTime(@now, 'LONGDATE') PRINT dbo.FormatDateTime(@now, 'LONGDATEANDTIME') PRINT dbo.FormatDateTime(@now, 'SHORTDATE') PRINT dbo.FormatDateTime(@now, 'SHORTDATEANDTIME') PRINT dbo.FormatDateTime(@now, 'UNIXTIMESTAMP') PRINT dbo.FormatDateTime(@now, 'YYYYMMDD') PRINT dbo.FormatDateTime(@now, 'YYYY-MM-DD') PRINT dbo.FormatDateTime(@now, 'YYMMDD') PRINT dbo.FormatDateTime(@now, 'YY-MM-DD') PRINT dbo.FormatDateTime(@now, 'MMDDYY') PRINT dbo.FormatDateTime(@now, 'MM-DD-YY') PRINT dbo.FormatDateTime(@now, 'MM/DD/YY') PRINT dbo.FormatDateTime(@now, 'MM/DD/YYYY') PRINT dbo.FormatDateTime(@now, 'DDMMYY') PRINT dbo.FormatDateTime(@now, 'DD-MM-YY') PRINT dbo.FormatDateTime(@now, 'DD/MM/YY') PRINT dbo.FormatDateTime(@now, 'DD/MM/YYYY') PRINT dbo.FormatDateTime(@now, 'HH:MM:SS 24') PRINT dbo.FormatDateTime(@now, 'HH:MM 24') PRINT dbo.FormatDateTime(@now, 'HH:MM:SS 12') PRINT dbo.FormatDateTime(@now, 'HH:MM 12') PRINT dbo.FormatDateTime(@now, 'goofy')``` Hope this will help you out , if it is reply back yo!!! My email ID; iloveuzak@yahoo.com Bye take care Regards, Zahed ZAK
 Re: Convert Decimal To Date? leoinfo21-Jul-08 2:37 leoinfo 21-Jul-08 2:37
 Re: Convert Decimal To Date? obarahmeh21-Jul-08 21:54 obarahmeh 21-Jul-08 21:54
 Re: Convert Decimal To Date? Mycroft Holmes21-Jul-08 23:52 Mycroft Holmes 21-Jul-08 23:52
 File size rezarafiee20-Jul-08 19:01 rezarafiee 20-Jul-08 19:01
 Re: File size Krish - KP20-Jul-08 20:58 Krish - KP 20-Jul-08 20:58
 Re: File size Ashfield20-Jul-08 21:00 Ashfield 20-Jul-08 21:00
 Nortwind database selection through database expert ??? amistry_petlad20-Jul-08 8:30 amistry_petlad 20-Jul-08 8:30
 Re: Nortwind database selection through database expert ??? Paul Conrad20-Jul-08 9:51 Paul Conrad 20-Jul-08 9:51
 Re: Nortwind database selection through database expert ??? [modified] amistry_petlad20-Jul-08 13:06 amistry_petlad 20-Jul-08 13:06
 Re: Nortwind database selection through database expert ??? Paddy Boyd21-Jul-08 2:30 Paddy Boyd 21-Jul-08 2:30
 Re: Nortwind database selection through database expert ??? Paul Conrad21-Jul-08 6:13 Paul Conrad 21-Jul-08 6:13
 Problem in a query Shuaib wasif khan19-Jul-08 23:18 Shuaib wasif khan 19-Jul-08 23:18
 Re: Problem in a query Colin Angus Mackay20-Jul-08 0:00 Colin Angus Mackay 20-Jul-08 0:00
 Re: Problem in a query Shuaib wasif khan20-Jul-08 0:54 Shuaib wasif khan 20-Jul-08 0:54
 Re: Problem in a query Blue_Boy20-Jul-08 2:50 Blue_Boy 20-Jul-08 2:50
 Re: Problem in a query Shuaib wasif khan20-Jul-08 4:40 Shuaib wasif khan 20-Jul-08 4:40
 Re: Problem in a query Wendelius20-Jul-08 9:29 Wendelius 20-Jul-08 9:29
 Last Visit: 31-Dec-99 18:00     Last Update: 14-Aug-24 21:01 Refresh ᐊ Prev1...1116111711181119112011211122112311241125 Next ᐅ