Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Problem with Dates !!!!!!!!!!!! Expand / Collapse
Author
Message
Posted Wednesday, December 25, 2013 5:09 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 1:01 PM
Points: 255, Visits: 625
Consider the below query

CREATE TABLE #TEMP (Id int,VALUE NVARCHAR(50))
INSERT INTO #TEMP VALUES(1,'')
INSERT INTO #TEMP VALUES(2,'2013/08')

DECLARE @FirstDate date,
@EndDate date,

SELECT @FirstDate=VALUE FROM #TEMP WHERE ID=1
SELECT @EndDate=VALUE FROM #TEMP WHERE ID=2

i want my FirstDate and @EndDate variable to store the values as both that of ' ' (blank) and a date.

I tried by kepping the datatype as Varchar but it did not work, i tried with convert but it too did not work.

So is it possible to make my variable such that it stores both ' '(blank) and a date.

Thanks
Post #1525882
Posted Wednesday, December 25, 2013 10:52 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 1:01 PM
Points: 255, Visits: 625
The closes i can come to this problem is

SELECT LEFT(CONVERT(DATE,VALUE,111),7) FROM #TEMP WHERE ID=2

still it is not working. Any one who can help !!!!!!!!
Post #1525906
Posted Wednesday, December 25, 2013 11:23 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 10:06 AM
Points: 346, Visits: 1,374
Shadab Shah (12/25/2013)
The closes i can come to this problem is

SELECT LEFT(CONVERT(DATE,VALUE,111),7) FROM #TEMP WHERE ID=2



I'm not entirely sure what you are trying to accomplish, but I think this is what you are looking for.

SELECT 
case when VALUE = '' then '' else
LEFT(CONVERT(DATE,VALUE + '/01',111),7)
end
FROM #temp



__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1525910
Posted Wednesday, December 25, 2013 7:00 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 1:01 PM
Points: 255, Visits: 625
I am trying to store the value return by the select in variable. I took that variable as varchar as well as date but nohing seems to work.....
Post #1525922
Posted Wednesday, December 25, 2013 8:57 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 4:01 PM
Points: 735, Visits: 4,703
If you wanted to store NULL and a DATE value, then it would be trivial. What's the point storing ''? Is that not the same as Null (unknown)? If not, what does BLANK mean?
Post #1525928
Posted Saturday, December 28, 2013 5:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:15 PM
Points: 7,131, Visits: 13,181
I strongly vote against a NVARCHAR data type to store date values (especially, with a length of 50 characters being allowed).
What's the reason for using NVARCHAR instead of DATETIME?




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1526287
Posted Tuesday, December 31, 2013 9:29 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 10:13 AM
Points: 649, Visits: 323
Hi.

1. The posters who said that a blank date is a bad idea are correct. If you are using the script as a demo to show what you are encountering in the application's data, then convert blanks to NULL with a CASE. Much easier to work with NULL.

2. I'm not sure that a DATE type of field will handle converting partial dates. I think that if the date is not valid, such as a year-month that we are implying from your example, then you must choose then to make the date a NULL (invalid), or if it is a "circa" date, you must complete it with a day.

On the other hand, if it is a JULIAN date, you need to do this (copied from http://www.ehow.com/how_12073756_convert-julian-date-sql.html):

CREATE FUNCTION convert_julian_to_datetime(@julian_date char(5)) RETURNS datetime AS
BEGIN
RETURN (SELECT DATEADD(day, CONVERT(int,RIGHT(@julian_date,3)) - 1, CONVERT(datetime, LEFT(@julian_date,2) + '0101', 112)))END

Thanks
John.
Post #1526784
Posted Wednesday, January 1, 2014 11:57 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:44 PM
Points: 36,959, Visits: 31,472
Shadab Shah (12/25/2013)
I am trying to store the value return by the select in variable. I took that variable as varchar as well as date but nohing seems to work.....


That is correct. You cannot store a blank in a DATE datatype. The blank will automatically be converted to 1900-01-01. To wit, you're simply using the wrong datatype for this problem.

If you have a reporting requirement, let's see the code for that instead of the simplified example that you original posted. Then, we might be able to help.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1526931
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse