Convert Decimal To Date?

obarahmeh

Dear All, I have a date values stored as Decimal in the SQL server 2005. I need to convert these decimal values into Date in the format (yyyy-mm-dd). Example: I need to convert 20080721 To 2008-07-21 I wrote the following statement for this purpose, but the result contains 00:00:00:00 in the end, and I think this is because I am using datetime function in the query. select convert(datetime, cast(DayDate AS char(8))) AS Date from TableName where DayDate = 20080721 Result from the previous query is: 2008-07-21 00:00:00:00, but I need it to be 2008-07-21 without 00:00:00:00

Kind Regards OBarahmeh

Rocky

hi, well as far as my experience goes, whenever u user datetime in SQL Server it always appends a default time, -- even if u're not specifying it. So I suggest u should stick to this result and try and improve the logic of ur front end to compensate to that. Hope it helps, Regards,

Rocky Success is a ladder which you can't climb with your hands in your pockets.

Blue_Boy

select substring(convert(varchar, convert(datetime, cast(DayDate AS char(8))),20),1,10) AS Date from TableName where DayDate = 20080721

I Love T-SQL "Don't torture yourself,let the life to do it for you."

zahedonline

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

leoinfo

As there is no *DATE* data type in SQL 2005, you cannot store a date in the form that you want. The only options you have to store a date (and time) are DATETIME and SMALLDATETIME (see Data Types[^]) If you need only the day part of a date I suggest you to use SMALLDATETIME ...

DECLARE @d DECIMAL(15,6) ;SET @d = 20080721.150825

SELECT
@d AS [as DECIMAL]
, CAST( LEFT(@d ,8) AS SMALLDATETIME ) AS [as SMALLDATETIME]
, CAST( LEFT(@d ,8) AS DATETIME ) AS [as DATETIME]
, CONVERT( NVARCHAR(10), CAST( LEFT(@d ,8) AS DATETIME ) , 121 ) AS [as NVARCHAR(10)]

Please... SAVE my time by rating the posts that you read!

There are 10 kinds of people in the world: those who understand binary and those who don't.

obarahmeh

Hello Every body, I've solved my problem... And this is the solution to convert a decimal to a date in the format (yyyy-mm-dd): select STUFF(STUFF(CAST(DayDate AS char(8)), 7, 0, '-'), 5, 0, '-') AS Date from TableName; ;P ;P :rose:

Kind Regards OBarahmeh

Mycroft Holmes

a more appropriate solution would be to use the date data type SELECT CONVERT(VARCHAR(20),CONVERT(DATETIME,'20080819'),106) SELECT CONVERT(VARCHAR(20),CONVERT(DATETIME,'20080819'),103) SELECT CONVERT(VARCHAR(20),CONVERT(DATETIME,'20080819'),110) SELECT CONVERT(VARCHAR(20),CONVERT(DATETIME,'20080819'),111)

Never underestimate the power of human stupidity RAH

Pete OHanlon

Rocky# wrote:

whenever u user datetime in SQL Server it always appends a default time

No it doesn't. You need to use GETDATE() or a fixed date if you don't supply a date into a non-nullable database field. [EDIT]Rocky# is right - we are talking at cross purposes here, and he's entirely right to say that there is no date only type in SQL Servers up to 2005. 2K8 has a date type which stores the date only, but datetime does what it says on the tin and has the time portion as part of it. My apologies for any confusion caused by my misreading of Rocky#'s post.[/EDIT]

Deja View - the feeling that you've seen this post before.

My blog | My articles

modified on Wednesday, July 23, 2008 7:08 AM

Rocky · modified on Wednesday, July 23, 2008 7:08 AM

my point is that datetime field always has a time part in it. try

Select Getdate()

or may be we're not on the same page at this point :suss:

Rocky Success is a ladder which you can't climb with your hands in your pockets.

Pete OHanlon

No, no, no. You're just plain wrong. GETDATE() is a system function, not field data. It's no different in effect to you typing SELECT 3. This doesn't mean that every integer field defaults to 3. Tell you what, as a test, run the following script:

USE [<<database>>]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].Test(
[ID] [int] IDENTITY(1,1) NOT NULL,
[Text] NVARCHAR(20) NOT NULL,
[Created] [datetime] NOT NULL,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
)
GO

INSERT INTO Test([Text]) VALUES ('Hello')

GO

Guess what. Created doesn't get a default value. What you do get is an error: Msg 515, Level 16, State 2, Line 2 Cannot insert the value NULL into column 'Created', table 'database.dbo.Test'; column does not allow nulls. INSERT fails. The statement has been terminated. So no default value there then. Please, before you pass information like this along, will you please check to see if you are right? [EDIT]Rocky# is right - we are talking at cross purposes here, and he's entirely right to say that there is no date only type in SQL Servers up to 2005. 2K8 has a date type which stores the date only, but datetime does what it says on the tin and has the time portion as part of it. My apologies for any confusion caused by my misreading of Rocky#'s post.[/EDIT]

Deja View - the feeling that you've seen this post before.

My blog | My articles

modified on Wednesday, July 23, 2008 7:12 AM

Rocky · modified on Wednesday, July 23, 2008 7:12 AM

Pete O'Hanlon wrote:

Guess what. Created doesn't get a default value. What you do get is an error:

:wtf: what else would it give, trying to insert a null into a not null column, try this

Insert into Test(text, created)
values ('my test', CAST('7/23/2008' as datetime))

The result looks like this on my end: 7/23/2008 12:00:00 AM My point was simple, if u r getting a dateTime object, it will ALWAYS show the time, if u dont wanna see the time then cast it to varchar or something but datetime will always show u the time. here in the above query I'm not giving the time but its still comming. Thats the simple point I was talking abt. Pardon me if this doesnt satisfy the guys original question but my point was this alright! :rose: I deeply respect ur place Pete, I dont mean any offense alright

Rocky Success is a ladder which you can't climb with your hands in your pockets.

Pete OHanlon

Crap. My apologies - I just reread your original post, and it seems you and I were talking about different things. So sorry - and I will amend my answers to reflect this. I really shouldn't post answers late at night.

Deja View - the feeling that you've seen this post before.

My blog | My articles

Rocky

No problem Pete! :)

Rocky Success is a ladder which you can't climb with your hands in your pockets.