April 2, 2010 at 6:06 am
Another issue you run into is this:
02-05-2010 is this February 5, 2010 or May 2, 2010?
Do you have a way to determine which is the correct interpretation of the dates?
April 2, 2010 at 6:09 am
Lynn Pettis (4/2/2010)
Another issue you run into is this:02-05-2010 is this February 5, 2010 or May 2, 2010?
Do you have a way to determine which is the correct interpretation of the dates?
This is 2nd February in my table
Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together
April 2, 2010 at 6:18 am
vaibhav.tiwari (4/2/2010)
I was asking that if i will use your query then it will give me null for all invalid date but that might be possible that that invalid date is like '25-01-2010' so it is proper date so i dont want to ommit that.
I posted more than one query 😉
I have an idea. Why don't you post some sample code, like I did, to populate a table variable with some test data, and show what output is required?
Otherwise, we are going to waste a lot of time guessing, and I bore easily 😉
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 2, 2010 at 8:35 am
Paul White NZ (4/2/2010)
vaibhav.tiwari (4/2/2010)
I was asking that if i will use your query then it will give me null for all invalid date but that might be possible that that invalid date is like '25-01-2010' so it is proper date so i dont want to ommit that.I posted more than one query 😉
I have an idea. Why don't you post some sample code, like I did, to populate a table variable with some test data, and show what output is required?
Otherwise, we are going to waste a lot of time guessing, and I bore easily 😉
Create Table #DeploymentFailure
(
ID int, UpdateID varchar(20), LogType char(1), LogDateTime VARCHAR(50)
)
INSERT INTO #DeploymentFailure
SELECT 23, 'a23', 'X', '11/25/08 12:33 AM'
UNION ALL
SELECT 34, 'x34', 'X', '25/11/08 12:33 AM'
UNION ALL
SELECT 12, 'y12', 'Y', '25-11-2008 12:33 AM'
UNION ALL
SELECT 56, 'y56', 'Z', '25-11-2008 00:33:00'
UNION ALL
SELECT 84, 'z84', 'Z', '11/25/2008 12:33 AM'
UNION ALL
SELECT 36, 'z36', 'Y', '25 Nov 2008 12:33 AM'
UNION ALL
SELECT 76, 'x76', 'X', 'Nov 25 2008 12:33 AM'
UNION ALL
SELECT 78, 'y78', 'Y', '25112008 12.33 AM'
SELECT id, updateid, logtype,
CASE WHEN ISDATE(LogDateTime) = 1 THEN CONVERT(DateTime,logDateTime) ELSE NULL END LogDateTime
FROM #DeploymentFailure
Now what should i do for null values in result set as those are also dates
not the garbage data.
and i have to do some date operation on those also so any how i need to convert them into date time.
Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together
April 2, 2010 at 8:41 am
vaibhav.tiwari (4/2/2010)
Paul White NZ (4/2/2010)
vaibhav.tiwari (4/2/2010)
I was asking that if i will use your query then it will give me null for all invalid date but that might be possible that that invalid date is like '25-01-2010' so it is proper date so i dont want to ommit that.I posted more than one query 😉
I have an idea. Why don't you post some sample code, like I did, to populate a table variable with some test data, and show what output is required?
Otherwise, we are going to waste a lot of time guessing, and I bore easily 😉
Create Table #DeploymentFailure
(
ID int, UpdateID varchar(20), LogType char(1), LogDateTime VARCHAR(50)
)
INSERT INTO #DeploymentFailure
SELECT 23, 'a23', 'X', '11/25/08 12:33 AM'
UNION ALL
SELECT 34, 'x34', 'X', '25/11/08 12:33 AM'
UNION ALL
SELECT 12, 'y12', 'Y', '25-11-2008 12:33 AM'
UNION ALL
SELECT 56, 'y56', 'Z', '25-11-2008 00:33:00'
UNION ALL
SELECT 84, 'z84', 'Z', '11/25/2008 12:33 AM'
UNION ALL
SELECT 36, 'z36', 'Y', '25 Nov 2008 12:33 AM'
UNION ALL
SELECT 76, 'x76', 'X', 'Nov 25 2008 12:33 AM'
UNION ALL
SELECT 78, 'y78', 'Y', '25112008 12.33 AM'
SELECT id, updateid, logtype,
CASE WHEN ISDATE(LogDateTime) = 1 THEN CONVERT(DateTime,logDateTime) ELSE NULL END LogDateTime
FROM #DeploymentFailure
Now what should i do for null values in result set as those are also dates
not the garbage data.
and i have to do some date operation on those also so any how i need to convert them into date time.
I notice all your sample dates are easily determined as to which is the month and which is the day. This goes back to my previous post, how do you know what date is represented by a date such as 2/5/2010? How do you know it is February 5th and not May 2nd, or are you just making an assumption that it is February 5th?
April 2, 2010 at 9:20 am
vaibhav.tiwari (4/2/2010)
Now what should i do for null values in result set as those are also dates not the garbage data.and i have to do some date operation on those also so any how i need to convert them into date time.
You will remember that I asked you for sample data and *required output*
I am not psychic (not quite, anyway) and neither, to the best of my knowledge, is Lynn 😀
Unless you clearly define what is, and what is not, acceptable as a date...how are we expected to know? The rules vary based on language, culture, system design...500,000 different things.
Help us out here.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 3, 2010 at 2:32 am
Paul White NZ (4/2/2010)
vaibhav.tiwari (4/2/2010)
Now what should i do for null values in result set as those are also dates not the garbage data.and i have to do some date operation on those also so any how i need to convert them into date time.You will remember that I asked you for sample data and *required output*
I am not psychic (not quite, anyway) and neither, to the best of my knowledge, is Lynn 😀
Unless you clearly define what is, and what is not, acceptable as a date...how are we expected to know? The rules vary based on language, culture, system design...500,000 different things.
Help us out here.
Yes I know that.
but unfortunately it was happened earlier now i can't do anything.
Now i will have to work on date operations for only valid dates which can be format in default.
Thank you all for your efforts and support.
Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together
April 3, 2010 at 2:48 am
vaibhav.tiwari (4/3/2010)
...only valid dates which can be format in default.
The point is that what is 'default' for you is not 'default' for me.
It depends on the settings I described earlier.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply