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 12»»

Need to find out whether date is of which data type Expand / Collapse
Author
Message
Posted Tuesday, January 8, 2013 3:21 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, February 18, 2013 1:47 AM
Points: 196, Visits: 87
Suppose I have a variable which contains date value in a string format.

eg: declare @value1 varchar(50) = '2013-01-08 15:44:12.2081606 +05:30'

Now my question is, how will i come to know whether the @value1 containing the date value as shown above is of which type? and based on the type it should return null, 1 or 0.
eg:
for datetime and datetimeoffset it should return 1,
for date type it should return 0,
and if @value1 doesn't contain any value then it should return NULL.


Post #1404092
Posted Tuesday, January 8, 2013 3:48 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, August 25, 2014 4:06 AM
Points: 314, Visits: 164
try using isDate() function.

or
try to convert the string using Convert function then check with isdate function.
Post #1404108
Posted Tuesday, January 8, 2013 3:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, February 18, 2013 1:47 AM
Points: 196, Visits: 87
If i use isdate() function also will not solve my issue.

my issue is to find out what is the datatype?
Post #1404110
Posted Tuesday, January 8, 2013 3:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, November 1, 2014 6:54 AM
Points: 5,221, Visits: 5,118
With difficulty

You could do a check in the string for the + or - symbol to indicate Offset

You could then do a check for a : to detail if a time has been put in the string for datetime

You could check the length of the variable is 10, if the date has been provided only




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1404114
Posted Tuesday, January 8, 2013 4:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, February 18, 2013 1:47 AM
Points: 196, Visits: 87
anthony.green (1/8/2013)
With difficulty

You could do a check in the string for the + or - symbol to indicate Offset

You could then do a check for a : to detail if a time has been put in the string for datetime

You could check the length of the variable is 10, if the date has been provided only


If user passes a string value with all these special characters will lead to wrong check.

eg: 'sdfsdfsfs:sdfsf', '2323232:2323:23:23 +2323'

Post #1404119
Posted Tuesday, January 8, 2013 4:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, November 1, 2014 6:54 AM
Points: 5,221, Visits: 5,118
You will need ISDATE(), LEN, CHARINDEX as a minumum to get this working.

But you should be doing your data validation in the front end, not within SQL.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1404122
Posted Tuesday, January 8, 2013 6:16 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, November 20, 2014 7:58 PM
Points: 3,422, Visits: 5,366
I agree with Anthony that this should be done in the front end. But you may want to examine the following results and see if this gives you some ideas.

WITH PotentialDates (d) AS (
SELECT '2013-01-08 15:44:12.2081606 +05:30'
UNION ALL SELECT '2013-01-08 15:44:12.2081606'
UNION ALL SELECT '2013-01-08 15:44:12.208'
UNION ALL SELECT '2013-01-08'
UNION ALL SELECT 'YYYY-DD-MM HH:MM:SS.mmmmmm'
)
SELECT IsDate=ISDATE(d)
,PATINDEX('[0-9][0-9][0-9][0-9][-][0-9][0-9][-][0-9][0-9]', LEFT(d, 10))
,ISDATE(LEFT(d, 23))
FROM PotentialDates


I found it interesting and somewhat unexpected that ISDATE() didn't work on the first two.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1404513
Posted Wednesday, January 9, 2013 1:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, November 1, 2014 6:54 AM
Points: 5,221, Visits: 5,118
ISDATE

Returns 1 if the expression is a valid date, time, or datetime value; otherwise, 0.

ISDATE returns 0 if the expression is a datetime2 value.


So as the first is a DateTimeOffset and the second is DateTime2, you would expect them to return 0.


DECLARE @String VARCHAR(50) = '2013-01-08 15:44:12.20848489 -05:30'
IF
PATINDEX('%[A-Z]%',@String) = 0
BEGIN
SELECT
CASE WHEN LEN(@String) = 10 THEN 'Date'
WHEN LEN(@String) = 23 THEN 'DateTime'
WHEN CHARINDEX('+',@String) > 0 THEN '+ Offset'
WHEN CHARINDEX('-',REVERSE(@String)) <=6 THEN '- Offset'
END AS WhatIsTheDataType
END
ELSE
SELECT 'You have not supplied a valid date'





Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1404595
Posted Wednesday, January 9, 2013 3:00 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, November 20, 2014 7:58 PM
Points: 3,422, Visits: 5,366
anthony.green (1/9/2013)
ISDATE

Returns 1 if the expression is a valid date, time, or datetime value; otherwise, 0.

ISDATE returns 0 if the expression is a datetime2 value.


You are assuming sir that I read BOL. It is much more fun to experiment.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1404629
Posted Wednesday, January 9, 2013 3:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, November 1, 2014 6:54 AM
Points: 5,221, Visits: 5,118
dwain.c (1/9/2013)
anthony.green (1/9/2013)
ISDATE

Returns 1 if the expression is a valid date, time, or datetime value; otherwise, 0.

ISDATE returns 0 if the expression is a datetime2 value.


You are assuming sir that I read BOL. It is much more fun to experiment.


Yep you are assuming that I read BOL too, I also fell into this trap when first looking at this yesterday.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1404631
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse