SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need to find out whether date is of which data type


Need to find out whether date is of which data type

Author
Message
vivekkumar341
vivekkumar341
SSC Veteran
SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)

Group: General Forum Members
Points: 252 Visits: 93
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.
Prassad Dabbada V R
Prassad Dabbada V R
Mr or Mrs. 500
Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)

Group: General Forum Members
Points: 520 Visits: 173
try using isDate() function.

or
try to convert the string using Convert function then check with isdate function.
vivekkumar341
vivekkumar341
SSC Veteran
SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)

Group: General Forum Members
Points: 252 Visits: 93
If i use isdate() function also will not solve my issue.

my issue is to find out what is the datatype?
anthony.green
anthony.green
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25594 Visits: 6519
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
When a question, really isn't a question - Jeff Smith
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


vivekkumar341
vivekkumar341
SSC Veteran
SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)

Group: General Forum Members
Points: 252 Visits: 93
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'
anthony.green
anthony.green
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25594 Visits: 6519
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
When a question, really isn't a question - Jeff Smith
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


dwain.c
dwain.c
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18453 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
anthony.green
anthony.green
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25594 Visits: 6519
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
When a question, really isn't a question - Jeff Smith
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


dwain.c
dwain.c
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18453 Visits: 6431
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. :-P It is much more fun to experiment. :-D


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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
anthony.green
anthony.green
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25594 Visits: 6519
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. :-P It is much more fun to experiment. :-D


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
When a question, really isn't a question - Jeff Smith
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search