How to replace string with stuff ?
-
Dear All, May i know how to replace other table time in to other date time Select EP_SCAN_DATE,CAST(STUFF(STUFF(EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME) as SCANDATE FROM AcmkIMS.dbo.EP_SCAN_HIST Result : 2012-01-06 04:39:19.000 Want actual result: example time is 09:00 replace in 2012-01-06 09:00 kindly advise
-
Dear All, May i know how to replace other table time in to other date time Select EP_SCAN_DATE,CAST(STUFF(STUFF(EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME) as SCANDATE FROM AcmkIMS.dbo.EP_SCAN_HIST Result : 2012-01-06 04:39:19.000 Want actual result: example time is 09:00 replace in 2012-01-06 09:00 kindly advise
Don't store dates as strings, and don't use string methods to manipulate dates. If you have any control over this database, you should change the
EP_SCAN_DATEcolumn to be adatetime. You haven't specified which DBMS you're using. Assuming MS SQL, you can use something like this:SELECT DateAdd(hour, 9, DateDiff(dd, 0, YourDateTimeColumn))
The
DateDiff(dd, 0, X)will return just the date part ofX, with the time set to midnight. You then useDateAddto add as many hours, minutes and seconds as you require.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Don't store dates as strings, and don't use string methods to manipulate dates. If you have any control over this database, you should change the
EP_SCAN_DATEcolumn to be adatetime. You haven't specified which DBMS you're using. Assuming MS SQL, you can use something like this:SELECT DateAdd(hour, 9, DateDiff(dd, 0, YourDateTimeColumn))
The
DateDiff(dd, 0, X)will return just the date part ofX, with the time set to midnight. You then useDateAddto add as many hours, minutes and seconds as you require.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
HI, i used MS SQL server 2008
In that case, you might want to consider changing the column type to datetime2(0)[^], which is two bytes smaller than a
datetime. Alternatively, if you never need the time portion of the column, you could change the type to date[^], which is even smaller. You'd then need to cast the value todatetime2(0)before adding the time:SELECT DateAdd(hour, 9, Convert(datetime2(0), YourDateColumn))
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Dear All, May i know how to replace other table time in to other date time Select EP_SCAN_DATE,CAST(STUFF(STUFF(EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME) as SCANDATE FROM AcmkIMS.dbo.EP_SCAN_HIST Result : 2012-01-06 04:39:19.000 Want actual result: example time is 09:00 replace in 2012-01-06 09:00 kindly advise
Let me reinforce what Richard has said, NEVER store date as text/varchar/char, ALWAYS use the correct data type. Text will give you endless problems whenever you try and manipulate the data. The date and datetime data types will give you the tools to manipulate the data easily (not simply it can take a bit of thinking to work out how to achieve a given result).
Never underestimate the power of human stupidity RAH
