英文:
Convert the dd-mm-yyyy format into yyyy-mm-dd format in T-SQL
问题
我必须使用SQL的isdate()函数。如果此函数找到有效的日期（yyyy-mm-dd），它将返回1。我有一个日期列，但格式为dd-mm-yyyy。因此，该函数无法正常工作。
在SSMS（SQL Server管理工具）中，我编写了以下查询：
declare @getdata varchar(max)
set @getdata= '[
{
"name": "21-01-1998",
"type": "Folder"
},
{
"name": "29-01-1998",
"type": "Folder"
},
{
"name": "299-01-1998",
"type": "Folder"
},
{
"name": "folder1",
"type": "Folder"
},
{
"name": "output",
"type": "Folder"
}
]'
然后，我使用openjson函数将JSON格式转换为表格结构：
select * from openjson(@getdata) with (foldername varchar(10) '$.name')
我正在编写这个查询，但由于我的日期格式为dd-mm-yyyy，所以没有得到结果：
select * from openjson(@getdata) with (foldername varchar(10) '$.name') where isdate(foldername) = 1
英文:
I have to use isdate() function of SQL. It will return 1 if this function found a valid date(yyyy-mm-dd). I have a date column but in dd-mm-yyyy format. So this function is not able to work.
In SSMS(SQL Server management studio) i write query
declare @getdata varchar(max)
set @getdata= '[
{
"name": "21-01-1998",
"type": "Folder"
},
{
"name": "29-01-1998",
"type": "Folder"
},
{
"name": "299-01-1998",
"type": "Folder"
},
{
"name": "folder1",
"type": "Folder"
},
{
"name": "output",
"type": "Folder"
}
]'
then i used openjson function to convert the json format into tabular structure
select * from openjson(@getdata) with (foldername varchar(10) '$.name')
I am writing this query and i am not getting result because my date is in dd-mm-yyy format.
select * from openjson(@getdata) with (foldername varchar(10) '$.name') where isdate(foldername) =1
答案1
得分: 0
你需要设置日期格式
SET DATEFORMAT dmy;
选择 ISDATE(foldername), * from openjson(@getdata) with (foldername varchar(10) '$.name')
其中 isdate(foldername) =1
英文:
you need to set dateformat
SET DATEFORMAT dmy;
select ISDATE(foldername), * from openjson(@getdata) with (foldername varchar(10) '$.name')
where isdate(foldername) =1
答案2
得分: -1
您可以使用CONVERT()函数将日期字符串转换为'yyyy-mm-dd'格式，然后再应用ISDATE()函数。
SELECT *
FROM OPENJSON(@getdata)
WITH (foldername VARCHAR(10) '$.name')
WHERE ISDATE(CONVERT(DATE, foldername, 105)) = 1;
英文:
You can use the CONVERT() function to convert the date string to the 'yyyy-mm-dd' format before applying the ISDATE() function.
SELECT *
FROM OPENJSON(@getdata)
WITH (foldername VARCHAR(10) '$.name')
WHERE ISDATE(CONVERT(DATE, foldername, 105)) = 1;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论