将日期格式转换为yyyy-mm-dd sql server c＃

I am creating a web app in which i want to convert the date format into yyyy-mm-dd

我正在创建一个Web应用程序，我想将日期格式转换为yyyy-mm-dd

currently i am getting date in the following format Saturday, July 1, 2017

目前，我将以2017年7月1日星期六的格式获取日期

here is my full select query

这是我的完整选择查询

SELECT 
    ROW_NUMBER() OVER (ORDER BY D.D_ID ) AS SL,
    D.D_DATE, D.USER_TP, D_ITEM_TYPE,D_AUTH_REF,D.D_ID, D.SUR_REFNO, D.REF_NO_DT,
    M.PART_NO,
    S.D_NAME,
    PART_DESC, DENOM, QTY_DEM, STORE_TYPE, ITEM_TYPE, EQUIPMENT,D_NO
FROM SURVEY_HDR D, DEMAND_STATUS S, STORE_MASTER M
WHERE 
    M.STORE_ID=D.PART_NO_ID
    AND S.D_ID=D.D_STATUS 
    AND D_DATE >= CONVERT(VarChar, 'Saturday, July 1, 2017',1)
    AND D_DATE <= CONVERT(VarChar, 'Monday, July 31, 2017',1)
ORDER BY D.D_ID

but i am getting the following error

但我收到以下错误

Msg 241, Level 16, State 1, Line 3 Conversion failed when converting date and/or time from character string.

消息241，级别16，状态1，行3转换在从字符串转换日期和/或时间时失败。

NOTE

注意

D_DATE is in Date format and date is being stored as '2011-01-01'

D_DATE采用日期格式，日期存储为“2011-01-01”

how can i convert the date?

我该如何转换日期？

4 个解决方案

#1

Your string cannot be recognized as a date. Try this:

您的字符串无法识别为日期。尝试这个：

declare @mydate varchar(22) = 'Saturday, July 1, 2017'
select cast(substring(@mydate,charindex(',',@mydate)+2,22)as date)

#2

Why should you convert the date in sql? You can return it as datetime and then format in your web page using toString

为什么要在sql中转换日期？您可以将其作为日期时间返回，然后使用toString在网页中进行格式化

Then in c# you can use:

然后在c＃中你可以使用：

  YourDateVar.ToString("yyyy-MM-dd");

#3

You are converting String to date but first you have to parse it in date than format it as you want :
Example:
1) Parse string to date :

您正在将String转换为日期，但首先您必须在日期中解析它而不是根据需要对其进行格式化：示例：1）解析日期字符串：

DateTime _date;
string strDate = "";
_date = DateTime.Parse("dddd, MMMM dd, yyyy"); // Saturday, July 1, 2017

2) Now convert this date to format you want :

2）现在将此日期转换为您想要的格式：

strDate = _date.ToString("yyyy-mm-dd"); // YOUR CASE

strDate = _date.ToString("MM/dd/yyyy");   // 07/21/2017 
strDate = _date.ToString("dddd, dd MMMM yyyy");   //Saturday, 21 July 2017
strDate = _date.ToString("dddd, dd MMMM yyyy HH:mm"); // Saturday, 21 July 2017 14:58
strDate = _date.ToString("HH:mm"); // 15:14
strDate = _date.ToString("hh:mm tt"); // 03:14 PM
strDate = _date.ToString("H:mm"); // 5:15
strDate = _date.ToString("h:mm tt"); // 3:16 PM
strDate = _date.ToString("HH:mm:ss"); // 15:16:29

#4

Convert both into the same format as "MM/dd/yyyy".

将两者转换为与“MM / dd / yyyy”相同的格式。

Change

更改

CONVERT(VarChar, 'Saturday, July 1, 2017',1) To cast(SUBSTRING ( CONVERT(VarChar, 'Saturday, July 1, 2017',1) ,CHARINDEX ( ',',CONVERT(VarChar, 'Saturday, July 1, 2017',1) )+1 , Len(CONVERT(VarChar, 'Saturday, July 1, 2017',1)) ) as date)

and

和

CONVERT(VarChar, 'Monday, July 31, 2017',1) To cast(SUBSTRING ( CONVERT(VarChar, 'Monday, July 31, 2017',1) ,CHARINDEX ( ',',CONVERT(VarChar, 'Monday, July 31, 2017',1) )+1 , Len(CONVERT(VarChar, 'Monday, July 31, 2017',1)) ) as date)

And Here is your query

这是您的查询

 SELECT 
        ROW_NUMBER() OVER (ORDER BY D.D_ID ) AS SL,
        D.D_DATE, D.USER_TP, D_ITEM_TYPE,D_AUTH_REF,D.D_ID, D.SUR_REFNO, D.REF_NO_DT,
        M.PART_NO,
        S.D_NAME,
        PART_DESC, DENOM, QTY_DEM, STORE_TYPE, ITEM_TYPE, EQUIPMENT,D_NO
    FROM SURVEY_HDR D, DEMAND_STATUS S, STORE_MASTER M
    WHERE 
        M.STORE_ID=D.PART_NO_ID
        AND S.D_ID=D.D_STATUS 
        AND Cast(D_DATE as Date) >= cast(SUBSTRING ( CONVERT(VarChar, 'Saturday, July 1, 2017',1) ,CHARINDEX ( ',',CONVERT(VarChar, 'Saturday, July 1, 2017',1) )+1 , Len(CONVERT(VarChar, 'Saturday, July 1, 2017',1)) ) as date)
        AND Cast(D_DATE as Date) <= cast(SUBSTRING ( CONVERT(VarChar, 'Monday, July 31, 2017',1) ,CHARINDEX ( ',',CONVERT(VarChar, 'Monday, July 31, 2017',1) )+1 , Len(CONVERT(VarChar, 'Monday, July 31, 2017',1)) ) as date)
    ORDER BY D.D_ID

#1